Calculating a running Balance for the transaction

We got a requirement to calculate the running total for every day or every month transaction from the transaction table and for the missing date we will carry forward the last balance.

See the example.

Below is the transaction table

TxnDate	TxnAmt
01-01-2025	100
01-02-2025	50
01-03-2025	-70
01-06-2025	300
01-10-2025	500
01-13-2025	-250
01-15-2025	100

We want to get the result as below

TxnDate	TxnAmt	Current Balance
01-01-2025	100	100
01-02-2025	50	150
01-03-2025	-70	80
01-04-2025	0	80
01-05-2025	0	80
01-06-2025	300	380
01-07-2025	0	380
01-08-2025	0	380
01-09-2025	0	380
01-10-2025	500	880
01-11-2025	0	880
01-12-2025	-250	630
01-13-2025	0	630
01-14-2025	100	730

Hope now clear with requirement.

Let’s see the Demo

For the demo we are creating a table and inserting some records as above.

CREATE TABLE Transactions (     TxnDate DATE,     Amount decimal(18,10) );

Table created successfully.

Inserting the records.

insert into Transactions(TxnDate,Amount) values ('01-01-2025',100 ), ('01-02-2025',50  ), ('01-03-2025',-70 ), ('01-06-2025',300 ), ('01-10-2025',500 ), ('01-13-2025',-250), ('01-15-2025',100 )

See the data into this table

Learn more about Running total:

Calculating the running total in sql server

https://bageshkumarbagi-msbi.blogspot.com/2016/11/calculating-running-total-in-sql-server.html

Let’s calculating running total using the above query logic.

SELECT TxnDate,Amount, SUM(Amount) OVER (ORDER BY TxnDate) AS [Current Balance] FROM Transactions

It is good to calculate the running total but it does not fulfill our requirement. For the missing day we are getting result like 4^th,5^th jan.

Let’s see how we can get this.

Ø  First we will create a temp table or CTE which generate the date series

Ø  Left join the transition table and for missing date we will provide Amount as 0

Ø  Then Finally we will calculate the running total.

Generating Date series

--Create Temp tale to store the date create table #dates (dt date) declare @mindt date,@maxdt date; set @mindt=(SELECT MIN(TxnDate) AS Dt    FROM Transactions); set @maxdt=(SELECT MAX(TxnDate) FROM Transactions); WITH Dates AS (     SELECT @mindt AS Dt      UNION ALL      SELECT DATEADD(DAY, 1, Dt)     FROM Dates     WHERE Dt < @maxdt ) insert into #dates(Dt) select Dt from Dates

 See the data in temp table

Now we will use left join and get the running total

SELECT         d.Dt AS TxnDate,         ISNULL(t.Amount, 0) AS Amount,         SUM(ISNULL(t.Amount, 0)) OVER (ORDER BY d.Dt) AS [Current Balance]     FROM #dates d     LEFT JOIN Transactions t         ON t.TxnDate = d.Dt

  

Got the expected result.