sql brains?

  • Sarah Brewster

    Member
    July 10, 2023 at 2:25 pm

    Here’s a starting point at least, from Victoria Yudin’s site.

    select
    T.CUSTNMBR [Customer ID],
    C.CUSTNAME [Customer Name],
    C.CUSTCLAS [Class ID],
    sum(case when year(T.DOCDATE) = 2015
    and month(T.DOCDATE) = 9
    then T.Amount else 0 end) [Sep 2015 Sales],
    sum(case when year(T.DOCDATE) = 2015
    and month(T.DOCDATE) = 9
    and T.RMDTYPAL = 1
    then 1 else 0 end) [Sep 2015 Invoices]

    from -- all posted RM transactions, exclude voids
    (select CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR, RMDTYPAL,
    case when RMDTYPAL in (1,3,4) then (SLSAMNT + MISCAMNT - TRDISAMT)
    when RMDTYPAL in (7,8) then -1*(SLSAMNT + MISCAMNT - TRDISAMT)
    else 0
    end Amount
    from RM20101
    where VOIDSTTS = 0
    union all
    select CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR, RMDTYPAL,
    case when RMDTYPAL in (1,3,4) then (SLSAMNT + MISCAMNT - TRDISAMT)
    when RMDTYPAL in (7,8) then -1*(SLSAMNT + MISCAMNT - TRDISAMT)
    else 0
    end Amount
    from RM30101
    where VOIDSTTS = 0) T

    left outer join RM00101 C -- customer master
    on T.CUSTNMBR = C.CUSTNMBR

    group by T.CUSTNMBR, C.CUSTNAME, C.CUSTCLAS

Welcome to our new site!

Here you will find a wealth of information created for people  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!