SQL Scripts for Microsoft Dynamics GP: Sales by Customer By Year

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns a list of sales in the last five years summarised by customer:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT	
	['RM Customer MSTR'].CUSTNMBR AS CustomerNumber
	,['RM Customer MSTR'].CUSTNAME AS CustomerName
	,['RM Customer MSTR'].CUSTCLAS AS CustomerClass
	,['RM_Class_MSTR'].CLASDSCR AS CustomerClassDescription
	,ISNULL(['RM Salesperson Master'].SLPRSNID, '* No Salesperson *') AS CustomerSalesperson
	,RTRIM(LTRIM(RTRIM(ISNULL(['RM Salesperson Master'].SLPRSNFN, '')) + ' ' + ISNULL(['RM Salesperson Master'].SPRSNSLN, ''))) AS CustomerSalesPersonName
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) THEN SLSAMNT ELSE 0 END) AS SalesYear0
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -1 THEN SLSAMNT ELSE 0 END) AS [SalesYear-1]
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -2 THEN SLSAMNT ELSE 0 END) AS [SalesYear-2]
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -3 THEN SLSAMNT ELSE 0 END) AS [SalesYear-3]
	,SUM(CASE WHEN YEAR(['Sales Data'].DOCDATE) = YEAR(GETDATE()) -4 THEN SLSAMNT ELSE 0 END) AS [SalesYear-4]
FROM
	(
		SELECT
			CUSTNMBR
			,DOCNUMBR
			,RMDTYPAL
			,DOCDATE
			,GLPOSTDT
			,CASE WHEN RMDTYPAL < 7 THEN
				SLSAMNT
			ELSE
				SLSAMNT * -1
			END AS SLSAMNT
		FROM
			RM20101 [sqlgreen]--[gptl=RM20101][/sqlgreen]
		WHERE
			RMDTYPAL <> 9
		AND
			VOIDSTTS = 0
		UNION ALL
			SELECT
				CUSTNMBR
				, DOCNUMBR
				, RMDTYPAL
				, DOCDATE
				, GLPOSTDT
				,CASE WHEN RMDTYPAL < 7 THEN
					SLSAMNT
				ELSE
					SLSAMNT * -1
				END AS SLSAMNT
			FROM
				RM30101 [sqlgreen]--[gptl=RM30101][/sqlgreen]
			WHERE
				RMDTYPAL <> 9
			AND
				VOIDSTTS = 0
	) AS ['Sales Data']
	INNER JOIN
		RM00101 ['RM Customer MSTR'] --RM Customer MSTR (RM00101)
			ON
				['Sales Data'].CUSTNMBR = ['RM Customer MSTR'].CUSTNMBR
	LEFT OUTER JOIN
		RM00301 AS ['RM Salesperson Master'] --RM Salesperson Master (RM00301)
			ON
				['RM Customer MSTR'].SLPRSNID = ['RM Salesperson Master'].SLPRSNID
	LEFT OUTER JOIN
		RM00201 AS ['RM_Class_MSTR'] --RM_Class_MSTR (RM00201)
			ON
				['RM Customer MSTR'].CUSTCLAS = ['RM_Class_MSTR'].CLASSID
GROUP BY
	['RM Customer MSTR'].CUSTNMBR
	,['RM Customer MSTR'].CUSTNAME
	,['RM Customer MSTR'].CUSTCLAS
	,['RM Salesperson Master'].SLPRSNID
	,['RM Salesperson Master'].SLPRSNFN
	,['RM Salesperson Master'].SPRSNSLN
	,['RM_Class_MSTR'].CLASDSCR
GO