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
