Day: June 30, 2023

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 simple RM Aged TB (Summary) report with figures calculated for the current, previous five periods and anything a column for anything older than this.

/*
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).
*/
DECLARE @DATE AS DATETIME = '2027-04-12'

SELECT DISTINCT
	['RM Debtor Master'].CUSTNMBR AS 'Customer'
	,['RM Debtor Master'].CUSTNAME AS 'Name'
	,ISNULL(
		(
			SELECT
					SUM(CASE WHEN ['RM Key Master'].RMDTYPAL in (1,3,4,5,6) THEN
						['RM Transaction Open'].CURTRXAM ELSE ['RM Transaction Open'].CURTRXAM * -1 END)
						
			FROM
				RM00401 AS ['RM Key Master'] --RM Key File (RM00401)
			LEFT JOIN
				RM20101 AS ['RM Transaction Open'] --RM Open File Debit Copy (RM20101)
					ON
						['RM Transaction Open'].RMDTYPAL = ['RM Key Master'].RMDTYPAL
					AND
						['RM Transaction Open'].DOCNUMBR = ['RM Key Master'].DOCNUMBR
					AND
						FORMAT(['RM Key Master'].DOCDATE, 'yyyyMM') = FORMAT(@DATE, 'yyyyMM')
			WHERE
				['RM Key Master'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
		)
	,0) AS 'Current'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
			AND
				FORMAT(DOCDATE, 'yyyyMM') = FORMAT(DATEADD(m, -1, @DATE), 'yyyyMM')
		)
	,0) AS '-1'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
			AND
				FORMAT(DOCDATE, 'yyyyMM') = FORMAT(DATEADD(m, -2, @DATE), 'yyyyMM')
		)
	,0) AS '-2'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
			AND
				FORMAT(DOCDATE, 'yyyyMM') = FORMAT(DATEADD(m, -3, @DATE), 'yyyyMM')
		)
	,0) AS '-3'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
			AND
				FORMAT(DOCDATE, 'yyyyMM') = FORMAT(DATEADD(m, -4, @DATE), 'yyyyMM')
		)
	,0) AS '-4'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
			AND
				FORMAT(DOCDATE, 'yyyyMM') = FORMAT(DATEADD(m, -5, @DATE), 'yyyyMM')
		)
	,0) AS '-5'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
			AND
				FORMAT(DOCDATE, 'yyyyMM') <= FORMAT(DATEADD(m, -6, @DATE), 'yyyyMM')
		)
	,0) AS 'Prior'
	,ISNULL(
		(
			SELECT
				SUM(CASE WHEN RMDTYPAL in (1,3,4,5,6) THEN CURTRXAM ELSE CURTRXAM * -1 END)
			FROM
				RM20101 AS ['RM Transaction Open']
			WHERE
				['RM Transaction Open'].CUSTNMBR = ['RM Debtor Master'].CUSTNMBR
		)
	,0) AS 'Total'
FROM
	RM00101 AS ['RM Debtor Master'] WITH (NOLOCK) --RM Customer MSTR (RM00101)
GO