SQL Scripts for Microsoft Dynamics GP: Select Debit, Credit and Net Change for All Accounts in Date Range

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 will allow the user to specify a date and return the debit, credit and net change for all accounts.

/*
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 @StartDate DATE = '2027-01-01'
DECLARE @EndDate DATE = '2027-03-31'

SELECT
	CASE WHEN ['Account Master'].ACTIVE = 1 THEN '' ELSE 'Yes' END AS 'Inactive'
	,['Account Index Master'].ACTNUMST AS 'Account'
	,['Account Master'].ACTDESCR AS 'Description'
	,(
		SELECT SUM(DEBITAMT) FROM
			(
		SELECT
			DEBITAMT
		FROM
			GL20000 AS ['Year-to-date transactions'] --Year-to-Date Transaction Open (GL20000)
		WHERE
			TRXDATE BETWEEN @StartDate AND @EndDate
		AND
			['Year-to-date transactions'].ACTINDX = ['Account Master'].ACTINDX
		UNION ALL
			SELECT
				DEBITAMT
			FROM
				GL30000 AS ['GL Transactions History'] --Account Transaction History (GL30000)
			WHERE
				TRXDATE BETWEEN @StartDate AND @EndDate
			AND
				['GL Transactions History'].ACTINDX = ['Account Master'].ACTINDX
			) AS ['GL Transactions']
	) AS 'Debit'
	,(
		SELECT SUM(CRDTAMNT) FROM
			(
		SELECT
			CRDTAMNT
		FROM
			GL20000 AS ['Year-to-date transactions']
		WHERE
			TRXDATE BETWEEN @StartDate AND @EndDate
		AND
			['Year-to-date transactions'].ACTINDX = ['Account Master'].ACTINDX
		UNION ALL
			SELECT
				CRDTAMNT
			FROM
				GL30000 AS ['GL Transactions History']
			WHERE
				TRXDATE BETWEEN @StartDate AND @EndDate
			AND
				['GL Transactions History'].ACTINDX = ['Account Master'].ACTINDX
			) AS ['GL Transactions']
	) AS 'Credit'
	,(
		SELECT SUM(NETCHANGE) FROM
			(
		SELECT
				DEBITAMT - (CRDTAMNT * 1) AS NETCHANGE
		FROM
			GL20000 AS ['Year-to-date transactions']
		WHERE
			TRXDATE BETWEEN @StartDate AND @EndDate
		AND
			['Year-to-date transactions'].ACTINDX = ['Account Master'].ACTINDX
		UNION ALL
			SELECT
				DEBITAMT - (CRDTAMNT * 1) AS NETCHANGE
			FROM
				GL30000 AS ['GL Transactions History']
			WHERE
				TRXDATE BETWEEN @StartDate AND @EndDate
			AND
				['GL Transactions History'].ACTINDX = ['Account Master'].ACTINDX
			) AS ['GL Transactions']
	) AS 'Net Change'
FROM
	GL00100 AS ['Account Master'] --Breakdown Account Master (GL00100)
INNER JOIN
	GL00105 AS ['Account Index Master'] --Account Index Master (GL00105)
		ON
			['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX