SQL View to Return Sales By Customer By Year

The last post was a SQL view to return purchases by vendor by year; in this one I am posting a similar view, but for the Sales series.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://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).
*/
IF OBJECT_ID (N'uv_AZRCRV_SalesByCustomerByYear', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesByCustomerByYear
GO
CREATE VIEW uv_AZRCRV_SalesByCustomerByYear AS
SELECT
	['Receivables Transactions'].CUSTNMBR AS 'Customer ID'
	,['RM Debtor MSTR'].CUSTNAME AS 'Customer Name'
	,['RM Debtor MSTR'].CUSTCLAS AS 'Class ID'
	,FORMAT(['Receivables Transactions'].DOCDATE, 'yyyy') AS 'Year'
	,CASE WHEN ['RM Debtor MSTR'].INACTIVE = 1 THEN
		'Inactive'
	ELSE
		'Active'
	END AS 'Status'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].SLSAMNT ELSE ['Receivables Transactions'].SLSAMNT * -1 END) AS 'Purchases Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TRDISAMT * -1 ELSE ['Receivables Transactions'].TRDISAMT END) AS 'Trade Discount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].FRTAMNT ELSE ['Receivables Transactions'].FRTAMNT * -1 END) AS 'Freight'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].MISCAMNT ELSE ['Receivables Transactions'].MISCAMNT * -1 END) AS 'Miscalleneous'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].TAXAMNT ELSE ['Receivables Transactions'].TAXAMNT * -1 END) AS 'Tax Amount'
	,SUM(CASE WHEN ['Receivables Transactions'].RMDTYPAL <= 3 THEN ['Receivables Transactions'].DOCAMNT ELSE ['Receivables Transactions'].DOCAMNT * -1 END) AS 'Total Amount'
FROM
	(SELECT
		CUSTNMBR
		,DOCDATE
		,GLPOSTDT
		,DOCNUMBR
		,RMDTYPAL
		,SLSAMNT
		,TRDISAMT
		,FRTAMNT
		,MISCAMNT
		,TAXAMNT
		,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
	FROM
		RM20101
	WHERE
		VOIDSTTS >= 0
	AND
		RMDTYPAL IN (1,3,4,7,8)
	UNION ALL
		SELECT
			CUSTNMBR
			,DOCDATE
			,GLPOSTDT
			,DOCNUMBR
			,RMDTYPAL
			,SLSAMNT
			,TRDISAMT
			,FRTAMNT
			,MISCAMNT
			,TAXAMNT
			,SLSAMNT - TRDISAMT + FRTAMNT + MISCAMNT + TAXAMNT AS DOCAMNT
		FROM
			RM30101
		WHERE
			VOIDSTTS >= 0
		AND
			RMDTYPAL IN (1,3,4,7,8)
	) AS ['Receivables Transactions']
LEFT JOIN
	RM00101 AS ['RM Debtor MSTR']
		ON
			['Receivables Transactions'].CUSTNMBR = ['RM Debtor MSTR'].CUSTNMBR
GROUP BY
	['Receivables Transactions'].CUSTNMBR
	,['RM Debtor MSTR'].CUSTNAME
	,['RM Debtor MSTR'].CUSTCLAS
	,['RM Debtor MSTR'].INACTIVE
	,FORMAT(['Receivables Transactions'].DocDate, 'yyyy')
GO
GRANT SELECT ON uv_AZRCRV_SalesByCustomerByYear TO DYNGRP
GO

The view can easily be plugged into SmartList Designer, SmartList Builder, a refreshable Excel Report, a SQL Server Reporting Services report or any other type of reporting tool.