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 code (on lines 5 through 16 below) to see more details about the logic I am using. The code below will give you 2007 through 2012 calendar years as well as ‘life-to-date’ total sales. You can add or remove years as needed following my example.

For more Dynamics GP Receivables code, take a look at my Receivables SQL Views page. Or check out my GP Reports page for views in other modules and additional report writing and coding tips.

CREATE VIEW view_Customer_Totals_by_Year
AS

-- *********************************************************************
-- Created Jan 9, 2012 by Victoria Yudin - Flexible Solutions, Inc.
-- For updates please check http://victoriayudin.com/gp-reports/
-- Includes all posted receivables transactions in GP
-- Document dates and calendar years are used for groupings
-- Voided transactions are excluded
-- Sales amount adds Invoices, Debit Memos and Finance Charges
--      and subtracts Credit Memos and Returns
-- Amount includes subtotal plus misc amount less trade discount
-- Only fucntional currency is shown
-- Note that Returns are assumed as 'on account', if this is not
--      typically the case, Returns should be excluded
-- Total columns shows all years, not just what is shown in the columns
-- *********************************************************************

SELECT
T.CUSTNMBR [Customer ID], C.CUSTNAME [Customer Name], C.CUSTCLAS [Class ID],
sum(case when YEAR(T.DOCDATE) = 2012 then T.Amount else 0 end) [2012 Sales],
sum(case when YEAR(T.DOCDATE) = 2011 then T.Amount else 0 end) [2011 Sales],
sum(case when YEAR(T.DOCDATE) = 2010 then T.Amount else 0 end) [2010 Sales],
sum(case when YEAR(T.DOCDATE) = 2009 then T.Amount else 0 end) [2009 Sales],
sum(case when YEAR(T.DOCDATE) = 2008 then T.Amount else 0 end) [2008 Sales],
sum(case when YEAR(T.DOCDATE) = 2007 then T.Amount else 0 end) [2007 Sales],
sum(T.Amount) [Total Sales]

FROM -- all posted RM transactions, exclude voids
(SELECT  CUSTNMBR, DOCDATE, GLPOSTDT, DOCNUMBR,
 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,
 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

GO
GRANT SELECT ON view_Customer_Totals_by_Year TO DYNGRP

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.