SQL Scripts for Microsoft Dynamics GP: Sales Report With Year Prior Comparison

Ian Grieve9 minute read0 comment

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

This script contains a SQL view which reports on sales transactions from one year compared against the prior year; it returns the number of transactions, number of items, costs, sales price and profit margins.

-- drop view if it exists
IF OBJECT_ID (N'uv_AZRCRV_SalesReportWithYearPriorComparison', N'V') IS NOT NULL
    DROP VIEW uv_AZRCRV_SalesReportWithYearPriorComparison
GO
-- create view
CREATE VIEW uv_AZRCRV_SalesReportWithYearPriorComparison AS
/*
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).
*/
WITH cteSalesReportData AS
	(
		SELECT
			FORMAT(['Sales Transaction History'].DOCDATE, 'yyyyMM') as YearMth
			,['Item Master'].ITEMNMBR
			,['Item Master'].ITEMDESC
			,['Item Master'].ITMCLSCD
			,['Sales Transaction Amounts History'].LOCNCODE
			,SUM(
					CASE WHEN ['Sales Transaction Amounts History'].SOPTYPE = 3 THEN
						['Sales Transaction Amounts History'].XTNDPRCE
					ELSE
						['Sales Transaction Amounts History'].XTNDPRCE * -1
					END
			) AS 'Sales In Period'
			,SUM(
					CASE WHEN ['Sales Transaction Amounts History'].SOPTYPE = 3 THEN
						['Sales Transaction Amounts History'].EXTDCOST
					ELSE
						['Sales Transaction Amounts History'].EXTDCOST * -1
					END
			) AS 'COGS In Period'
			,SUM(['Sales Transaction Amounts History'].MARGINPERC) / SUM(['Sales Transaction Amounts History'].NUMBER) AS 'Margin %'
			,SUM(['Sales Transaction Amounts History'].QTYTOINV) AS 'Quantity'
			,COUNT(['Sales Transaction History'].SOPNUMBE) AS 'TrxCount'
		FROM
			IV00101 AS ['Item Master']
		LEFT JOIN
			(
				SELECT
					SOPNUMBE
					,SOPTYPE
					,LOCNCODE
					,ITEMNMBR
					,CMPNTSEQ
					,XTNDPRCE
					,EXTDCOST
					,CASE WHEN EXTDCOST = 0 THEN 100 ELSE (XTNDPRCE / EXTDCOST) * 100 END AS MARGINPERC
					,QTYTOINV
					,1 AS NUMBER
				FROM
					SOP10200
				UNION ALL
					SELECT
						SOPNUMBE
						,SOPTYPE
						,LOCNCODE
						,ITEMNMBR
						,CMPNTSEQ
						,XTNDPRCE
						,EXTDCOST
						,CASE WHEN EXTDCOST = 0 THEN 100 ELSE (XTNDPRCE / EXTDCOST) * 100 END AS MARGINPERC
						,QTYTOINV
						,1 AS NUMBER
					FROM
						SOP30300
			) AS ['Sales Transaction Amounts History']
				ON
					['Sales Transaction Amounts History'].ITEMNMBR = ['Item Master'].ITEMNMBR
				AND
					['Sales Transaction Amounts History'].CMPNTSEQ = 0
				AND
					['Sales Transaction Amounts History'].SOPTYPE IN (3,4)
		LEFT JOIN
			(
				SELECT
					SOPNUMBE
					,SOPTYPE
					,DOCID
					,DOCDATE
				FROM
					SOP10100
				UNION ALL
					SELECT
						SOPNUMBE
						,SOPTYPE
						,DOCID
						,DOCDATE
					FROM
						SOP30200
			) AS ['Sales Transaction History']
				ON
					['Sales Transaction History'].SOPNUMBE = ['Sales Transaction Amounts History'].SOPNUMBE
				AND
					['Sales Transaction History'].SOPTYPE = ['Sales Transaction Amounts History'].SOPTYPE
		GROUP BY
			FORMAT(['Sales Transaction History'].DOCDATE, 'yyyyMM')
			,['Item Master'].ITEMNMBR
			,['Item Master'].ITEMDESC
			,['Item Master'].ITMCLSCD
			,['Sales Transaction Amounts History'].LOCNCODE
	)

SELECT
	DB_NAME() AS 'Company'
	,['This Year'].LOCNCODE AS 'Site'
	,['This Year'].YearMth
	,['This Year'].ITEMNMBR AS 'Item Number'
	,['This Year'].ITEMDESC AS 'Item Description'
	,['This Year'].ITMCLSCD AS 'Item Class'
	,ISNULL(['This Year'].TrxCount, 0) AS 'Trx TY'
	,ISNULL(['Last Year'].TrxCount, 0) AS 'Trx LY'
	,ISNULL(['This Year'].Quantity, 0) AS 'Qty of Item TY'
	,ISNULL(['Last Year'].Quantity, 0) AS 'Qty of Item LY'
	,ISNULL(['This Year'].[Sales In Period], 0) AS 'Sales This Year'
	,ISNULL(['Last Year'].[Sales In Period], 0) AS 'Sales Last Year'
	,ISNULL(['This Year'].[COGS In Period], 0) AS 'Cost This Year'
	,ISNULL(['Last Year'].[COGS In Period], 0) AS 'Cost Last Year'
	,ISNULL(['This Year'].[Sales In Period], 0) - ISNULL(['This Year'].[COGS In Period], 0) AS 'Margin This Year'
	,ISNULL(['Last Year'].[Sales In Period], 0) - ISNULL(['Last Year'].[COGS In Period], 0) AS 'Margin Last Year'
	,CASE WHEN ISNULL(['This Year'].[COGS In Period], 0) = 0 THEN
		0
	ELSE
		(ISNULL(['This Year'].[Sales In Period], 0) / ISNULL(['This Year'].[COGS In Period], 0)) * 100 
	END AS 'Margin % This Year'
	,CASE WHEN ISNULL(['Last Year'].[COGS In Period], 0) = 0 THEN
		0
	ELSE
		(ISNULL(['Last Year'].[Sales In Period], 0) / ISNULL(['Last Year'].[COGS In Period], 0)) * 100 
	END AS 'Margin % Last Year'
FROM
	cteSalesReportData AS ['This Year']
LEFT JOIN
	cteSalesReportData AS ['Last Year']
		ON
			['Last Year'].ITEMNMBR = ['This Year'].ITEMNMBR
		AND
			['Last Year'].YearMth = ['This Year'].YearMth - 100 -- subtract 100 from 202003 to get March last year
		AND
			['Last Year'].LOCNCODE = ['This Year'].LOCNCODE
WHERE
	['This Year'].YearMth IS NOT NULL
UNION
	SELECT
		DB_NAME() AS 'Company'
		,['Last Year'].LOCNCODE AS 'Site'
		,['Last Year'].YearMth + 100 AS YearMth
		,['Last Year'].ITEMNMBR AS 'Item Number'
		,['Last Year'].ITEMDESC AS 'Item Description'
		,['Last Year'].ITMCLSCD AS 'Item Class'
		,ISNULL(['This Year'].TrxCount, 0) AS 'Trx TY'
		,ISNULL(['Last Year'].TrxCount, 0) AS 'Trx LY'
		,ISNULL(['This Year'].Quantity, 0) AS 'Qty of Item TY'
		,ISNULL(['Last Year'].Quantity, 0) AS 'Qty of Item LY'
		,ISNULL(['This Year'].[Sales In Period], 0) AS 'Sales This Year'
		,ISNULL(['Last Year'].[Sales In Period], 0) AS 'Sales Last Year'
		,ISNULL(['This Year'].[COGS In Period], 0) AS 'Cost This Year'
		,ISNULL(['Last Year'].[COGS In Period], 0) AS 'Cost Last Year'
		,ISNULL(['This Year'].[Sales In Period], 0) - ISNULL(['This Year'].[COGS In Period], 0) AS 'Margin This Year'
		,ISNULL(['Last Year'].[Sales In Period], 0) - ISNULL(['Last Year'].[COGS In Period], 0) AS 'Margin Last Year'
		,CASE WHEN ISNULL(['This Year'].[COGS In Period], 0) = 0 THEN
			0
		ELSE
			(ISNULL(['This Year'].[Sales In Period], 0) / ISNULL(['This Year'].[COGS In Period], 0)) * 100 
		END AS 'Margin % This Year'
		,CASE WHEN ISNULL(['Last Year'].[COGS In Period], 0) = 0 THEN
			0
		ELSE
			(ISNULL(['Last Year'].[Sales In Period], 0) / ISNULL(['Last Year'].[COGS In Period], 0)) * 100 
		END AS 'Margin % Last Year'
	FROM
		cteSalesReportData AS ['Last Year']
	LEFT JOIN
		cteSalesReportData AS ['This Year']
			ON
				['Last Year'].ITEMNMBR = ['This Year'].ITEMNMBR
			AND
				['Last Year'].YearMth = ['This Year'].YearMth -100 -- subtract 100 from 202003 to get March last year
			AND
				['Last Year'].LOCNCODE = ['This Year'].LOCNCODE
	WHERE
		['This Year'].YearMth IS NULL
	AND
		['Last Year'].YearMth <= FORMAT(GETDATE(), 'yyyyMM') - 100
GO

-- grant select permissions to DYNGRP
GRANT SELECT ON uv_AZRCRV_SalesReportWithYearPriorComparison TO DYNGRP
GO

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Looking for support or consultancy with Microsoft Dynamics GP?

I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.
PrintFriendlyLinkedin
Related Posts
SQL Scripts for Microsoft Dynamics GP: Script Showing Quantities on Work Status Sales Transactions
SQL Scripts for Microsoft Dynamics GP: Get Items Showing Quantities By Type
SQL Scripts for Microsoft Dynamics GP: Update Accounts and Distributions on Work Status Sales Transactions from Item Card, Tax Details or Posting Account Setup
SQL View to Get Assembly BOM Items for Sales Transactions in Microsoft Dynamics GP
SQL View to Return Sales Invoice Lines
SQL Scripts for Microsoft Dynamics GP: Status of Items Based on Serial Number
SQL Scripts for Microsoft Dynamics GP: Purchased Items With Serial Numbers and Linked Sales Transactions
SQL View on Sales Order Processing Backorder Transactions

Leave a Reply

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