SQL Scripts for Microsoft Dynamics GP: Return Opening Balance for Period of Supplied Date

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.

I don’t recall for whom or why this script was returned, but it will return the opening balance for every G/L account for the user supplied date.

/*
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-04-01'

SELECT
	['Account Index Master'].ACTNUMST AS 'Account'
	,CASE ACCTTYPE
		WHEN 1 THEN 'Posting Account'
		WHEN 2 THEN 'Unit Account'
		WHEN 3 THEN 'Posting Allocation Account'
		WHEN 4 THEN 'Unit Allocation Account'
		ELSE 'Unknown Account Type'
	END AS AccountType
	,['Account Master'].ACTDESCR AS 'Description'
	,ISNULL((
		SELECT
			SUM(['Account Sumary Master'].PERDBLNC)
		FROM
			SY40100 AS SY --Period Setup (SY40100)
		INNER JOIN
			SY40100 AS SYCUR --Period Setup (SY40100)
				ON
					SY.SERIES = 2
				AND
					CONVERT(DATETIME, CONVERT(VARCHAR(100), @StartDate, 126), 126) BETWEEN SYCUR.PERIODDT AND SYCUR.PERDENDT			
				AND
					SYCUR.ODESCTN = 'General Entry'
		INNER JOIN
				(
					SELECT
						PERDBLNC
						,YEAR1
						,PERIODID
						,ACTINDX
					FROM
						GL10110 --Account Current Summary Master (GL10110)
					UNION ALL
						SELECT
							PERDBLNC
							,YEAR1
							,PERIODID
							,ACTINDX
						FROM
							GL10111 --Account Summary History (GL10111)
				) AS ['Account Sumary Master']
			ON
				['Account Sumary Master'].ACTINDX = ['Account Index Master'].ACTINDX
			AND
				['Account Sumary Master'].YEAR1 = SYCUR.YEAR1
			AND
				['Account Sumary Master'].PERIODID <= CASE WHEN SYCUR.PERIODID = 1 THEN 0 ELSE SY.PERIODID END
		WHERE
			SY.SERIES = 2
		AND
			DATEADD(month, -1, CONVERT(DATETIME, CONVERT(VARCHAR(100), @StartDate, 126), 126)) BETWEEN SY.PERIODDT AND SY.PERDENDT
		AND
			SY.ODESCTN = 'General Entry'
	), 0) AS 'Beginning Balance'
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
GO