# SQL Server 2014: I have an opening balance. I need to calculate the final balance. Total accumulated by month rows.

You must use a recursive CTE to produce the accumulated total. Basically, set the end of row 1 of each group of accounts using the formula begbal + credit + debit, then, in all subsequent rows of that account, you substitute the begbal value of the previous rows.

Prepare:

``````CREATE TABLE Accounts (
Acct INT,
INT year,
Prd INT,
begbal int,
INT debit,
INT credit
)
TO 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)
TO GO
``````

Query:

``````WITH AccountBlanges AS (
Select the account
Year,
Prd
begbal
debit,
credit,
ROW_NUMBER () OVER (PARTITION BY a1.Acct ORDER BY Year, Prd) AS Rn
OF Accounts a1
), RunningBalances AS (
SELECT a1.Act,
a1 years
a1.prd,
a1.begbal,
a1.debit,
a1.credito,
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.Act,
a1 years
a1.prd,
a1.begbal,
a1.debit,
a1.credito,
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 the account
Year,
Prd
begbal
debit,
credit,
endbal
FROM RunningBalances
ORDER BY ACCOUNT, Year, Prd
``````

Exit:

``````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
``````