Update query for beginning balances

Transact-SQL
1

I am looking for an update query that will update my Beginning balances in a table. The data from source does not have beginning balances in periods other than period 1. so I need to run an update on my temp table to fill in the beginning balances based on the first period.

If the ending balance of period one is 2346.93 then the beginning balance for period 2 should be 2346.93. Then recalculating the Ending balance for period two based on the Debit and Credit amounts then updating period three beginning balance with period two's ending balance - and so on for that account and Fiscal year.

Thank you
Stephen Archer

image
image.png823×270 9.18 KB

2

Which specific version of SQL Server? 2008? 2012? etc. It makes a difference to the best (and easiest) way to do this.

3

I have the 2017 -

thank you
Stephen

4 
drop table if exists #accounts;
create table #accounts (
    account varchar(30) null,
    accounttype varchar(30) null,
    fiscalyear smallint null,
    fiscalperiod tinyint null,
    beginningbalance decimal(9, 2) null,
    debitamount decimal(9, 2) null,
    creditamount decimal(9, 2) null,
    endingbalance decimal(9, 2) null
)
insert into #accounts values
('10010-000-000-001', 'Operating', 2019, 1,2346.93,172172.07,172172.07,2346.93),
('10010-000-000-001', 'Operating', 2019, 2,0,69826.39,69843.45,-17.06),
('10010-000-000-001', 'Operating', 2019, 3,0,11186.21,11194.77,-8.56),
('10010-000-000-001', 'Operating', 2019, 4,0,109813.02,109821.54,-8.52),
('10010-000-000-001', 'Operating', 2019, 5,0,122499.23,124007.76,-1508.53),
('10010-000-000-001', 'Operating', 2019, 6,0,120999.23,121006.58,-7.35),
('10010-000-000-001', 'Operating', 2019, 7,0,283152.01,283160.03,-8.02),
('10010-000-000-001', 'Operating', 2019, 8,0,1439362.83,1435151.83,4211),
('10010-000-000-001', 'Operating', 2019, 9,0,128005.52,128065.27,-59.75),
('10010-000-000-001', 'Operating', 2019,10,0,128005.53,131945.41,-3939.88),
('10010-000-000-001', 'Operating', 2019,11,0,129277.84,129277.99,-0.15),
('10010-000-000-001', 'Operating', 2019,12,0,134518.85,134537.70,-18.85)

;WITH cte_balances_forward AS (
    SELECT
        SUM(beginningbalance + debitamount - creditamount) 
            OVER(PARTITION BY account, accounttype ORDER BY fiscalperiod
            RANGE UNBOUNDED PRECEDING) AS newbalance,
        *
    FROM #accounts
)
SELECT
    account, accounttype,
    fiscalyear, fiscalperiod,
    ISNULL(LAG(newbalance, 1) OVER(PARTITION BY account, accounttype 
        ORDER BY fiscalperiod), beginningbalance) AS beginningbalance,
    --*/
    debitamount, creditamount,
    --endingbalance AS chgtoendingbalance,
    newbalance AS endingbalance
FROM cte_balances_forward
ORDER BY account, accounttype, fiscalperiod