Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Sarah Brewster]

Up

0

Down

::

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 …