SQL View to Return Sales By Customer By Year

Microsoft Dynamics GPThe 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.

Can ISC Software help?

ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you would like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.

Your Name

Your Email

Subject

Your Enquiry

What should we write about next?

Your Name

Your Email

Suggested Topic

Suggestion Details

5 thoughts on “SQL View to Return Sales By Customer By Year

  1. Pingback: Comment
  2. Pingback: Comment
  3. Pingback: Comment
  4. Pingback: Comment
  5. Pingback: Comment

Leave a Reply

Your email address will not be published. Required fields are marked *