I have a beginning balance I need to calculate ending balance running total by month rows

Question

I have been searching this site and others trying to find an answer to this. I tried various things but cannot get my head around an answer so here goes. Here my data:

What I need is to start with the begbal of each Acct and add the debits, subtract the credits and produce an ending balance of that month (row). When the Acct changes, pick up the new begbal and start again. With the data it should look like this:

I can get a running total on the debits and credits by doing this:

SELECT
pph.Acct,
pph.Year, 
pph.Prd,
pph.begbal,
pph.debit,
pph.credit,
SUM(pph.debit+pph.credit) OVER (PARTITION BY pph.Acct ORDER BY pph.Year, pph.Prd) AS endbal
FROM GL_PeriodPostingHistory pph

What I want to do is

If Acct <> prevoius Acct
then Sum( begbal+debit-credit)
else Sum(previous endbal+debit-credit) as endbal

and I just can't figure out how.

Answer 1

You can use the FIRST_VALUE window function along with SUM(pph.debit-pph.credit) to get your desired output.

SELECT
pph.Acct,
pph.Year, 
pph.Prd,
pph.begbal,
pph.debit,
pph.credit,
FIRST_VALUE(pph.begbal) OVER (PARTITION BY pph.Acct ORDER BY pph.Year, pph.Prd)
    + SUM(pph.debit-pph.credit) OVER (PARTITION BY pph.Acct ORDER BY pph.Year, pph.Prd) AS endbal
FROM dbo.GL_PeriodPostingHistory pph;

db fiddle link.

Thanks to HandyD for the sample data written as T-SQL.

Answer 2

You should use a recursive CTE to produce the running total. Basically, you set the endbal for row 1 of each Acct group using the begbal + credit + debit formula, then on all subsequent rows for that account you substitute the previous rows endbal value for the begbal value.

Setup:

CREATE TABLE Accounts (
    Acct INT,
    Year INT,
    Prd INT,
    begbal INT,
    debit INT,
    credit INT
)
GO
INSERT INTO Accounts
VALUES (1, 2017, 1, -134, 0, 0),
(1, 2017, 10, 0, 0, 20),
(1, 2017, 11, 0, 0, 186),
(1, 2018, 1, -340, 17, 14),
(1, 2018, 4, 0, 0, 7),
(1, 2018, 6, 0, 0, 33),
(1, 2018, 12, 0, 0, 152),
(1, 2019, 1, -529, 0, 0),
(2, 2014, 1, 1000, 0, 0),
(2, 2015, 1, 1000, 0, 0),
(2, 2015, 5, 0, 0, 950),
(2, 2016, 1, 50, 0, 0),
(2, 2017, 1, 50, 0, 0),
(2, 2018, 1, 50, 0, 0),
(2, 2019, 1, 50, 0, 0)
GO

Query:

WITH AccountBalances AS (
    SELECT Acct,
        Year,
        Prd,
        begbal,
        debit,
        credit,
        ROW_NUMBER() OVER (PARTITION BY a1.Acct ORDER BY Year, Prd) AS Rn
    FROM Accounts a1
), RunningBalances AS (
    SELECT a1.Acct,
        a1.Year,
        a1.Prd,
        a1.begbal,
        a1.debit,
        a1.credit,
        a1.begbal + SUM(a1.debit + a1.credit) OVER (PARTITION BY a1.Acct ORDER BY a1.Year, a1.Prd) AS endbal,
        a1.rn
    FROM AccountBalances a1
    WHERE Rn = 1
    UNION ALL
    SELECT a1.Acct,
        a1.Year,
        a1.Prd,
        a1.begbal,
        a1.debit,
        a1.credit,
        a2.endbal + SUM(a1.debit + a1.credit) OVER (PARTITION BY a1.Acct ORDER BY a1.Year, a1.Prd) AS endbal,
        a1.rn
    FROM AccountBalances a1
    INNER JOIN RunningBalances a2 ON a2.Acct = a1.Acct AND a2.Rn = a1.Rn - 1
)

SELECT Acct,
    Year,
    Prd,
    begbal,
    debit,
    credit,
    endbal
FROM RunningBalances
ORDER BY Acct, Year, Prd

Output:

Acct    Year    Prd     begbal  debit   credit  endbal
------------------------------------------------------
1       2017    1       -134    0       0       -134
1       2017    10      0       0       20      -114
1       2017    11      0       0       186     72
1       2018    1       -340    17      14      103
1       2018    4       0       0       7       110
1       2018    6       0       0       33      143
1       2018    12      0       0       152     295
1       2019    1       -529    0       0       295
2       2014    1       1000    0       0       1000
2       2015    1       1000    0       0       1000
2       2015    5       0       0       950     1950
2       2016    1       50      0       0       1950
2       2017    1       50      0       0       1950
2       2018    1       50      0       0       1950
2       2019    1       50      0       0       1950