Generate a GL running sum for a specific year and account

We recently had a requirement to report on the running sum of a certain GL account (it was the cash account)

We needed the BBF entry, and then a running sum of all the periods after that. Problem is, you don't know where to look for the BBF. In January it might be in history or open. Also, the periods that we need to report from might also be in history or open. 

The code below handles all of that. It can easily be expanded to return more accounts, and restricted to fewer months. 

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

DECLARE @StartYear INT = 2019
 
SELECT Year1, PERIODID, ACTINDX, CRDTAMNT, DEBITAMT,
       -- Generate a running total
       SUM(ISNULL(DEBITAMT, 0) - ISNULL(CRDTAMNT, 0)) OVER (ORDER BY YEAR1, PeriodID) AS _Value
    FROM (
        --3 queries unioned together
        --Get the BBF for the year
        --we don't know exactly when they'll move the current year to history, so we'll always look in history and open
        SELECT Year1, PERIODID, ACTINDX, SUM(CRDTAMNT) AS CRDTAMNT, SUM(DEBITAMT) AS DEBITAMT
            FROM (
                SELECT OPENYEAR AS Year1, PERIODID, ACTINDX, CRDTAMNT, DEBITAMT
                    FROM GL20000
                    --get the BBF line(s)
                    WHERE PERIODID = 0
                        AND ACTINDX = 144
                UNION ALL
                SELECT HSTYEAR, PERIODID, ACTINDX , CRDTAMNT, DEBITAMT
                    FROM GL30000
                    WHERE PERIODID = 0
                        AND HSTYEAR = @StartYear
                        AND ACTINDX = 144
            ) BBF
            GROUP BY PERIODID, Year1, ACTINDX
        UNION ALL
        --the year in question may or may not be in history
        SELECT HSTYEAR AS Year1,  PERIODID, ACTINDX, SUM(CRDTAMNT) AS CRDTAMNT, SUM(DEBITAMT) AS DEBITAMT
            FROM GL30000
            WHERE PERIODID <> 0
                AND HSTYEAR >= @StartYear
                AND ACTINDX = 144
            GROUP BY PERIODID, HSTYEAR, ACTINDX
        UNION ALL
        --the year in question may or may not be in open
        SELECT OPENYEAR AS Year1, PERIODID, ACTINDX, SUM(CRDTAMNT) AS CRDTAMNT, SUM(DEBITAMT) AS DEBITAMT
            FROM GL20000
            WHERE PERIODID <> 0
                AND ACTINDX = 144
            GROUP BY PERIODID, OPENYEAR, ACTINDX
        ) E
        ORDER BY 1, 2

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences