user1810355 user1810355 - 1 year ago 52
SQL Question

SQL Running total with reset

I have a table with dates and transactions. I need to calculate running total (which would be easy with sum partition by) except it needs to reset to zero when there is negative value in "amount" and then start calculating running total again. Any ideas?

account|amount|     date          |total|
123456 |50 |2017-01-01 00:00:00|50
123456 |50 |2017-01-02 00:00:00|100
123456 |100 |2017-01-03 00:00:00|200
123456 |-50 |2017-01-04 00:00:00|0
123456 |100 |2017-01-05 00:00:00|100
123456 |100 |2017-01-06 00:00:00|200

Answer Source

With update variable method you can calculate running total with reset option.

declare @tblItems table(
 ID int identity(1, 1),
 Amount decimal(12, 3),
 RunningTotal decimal(12, 3) default(0)
)

insert into @tblItems(Amount)
values
 (50),
 (20),
 (-100),
 (5),
 (10)
;

DECLARE @RunningTotal decimal(12, 3) = 0;

UPDATE @tblItems
SET @RunningTotal = RunningTotal = case when amount<0 then 0 else (@RunningTotal + Amount) end
FROM @tblItems;

SELECT ID, Amount, RunningTotal
FROM @tblItems
ORDER BY ID;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download