::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
victoriayudin.com
SQL view for customer yearly totals in Dynamics GP
As a follow up to my SQL view for vendor yearly totals, here is something similar for customers. I have combined sales less credits/returns in one column, please take a look at the notes above the …