SQL Running-Total with reset option

I received this question from one of my colleague and found worth sharing with you. The scenario is that we need to display the running total for amount field which could be done in different ways, as discussed in the post Calculate Running Total. But the requirement slightly differs in this case, because we need to reset running-total field if it encounters any negative value.

Lets start with a simple example.

We have a table @tblItems with some dummy data as follows:

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)
;

Now if you apply the technique Update with Local Variable, as discussed in the post mentioned earlier:

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

UPDATE @tblItems
SET @RunningTotal = RunningTotal = @RunningTotal + Amount
FROM @tblItems;

SELECT ID, Amount, RunningTotal
FROM @tblItems
ORDER BY ID;

You will get output like this:

But the problem is that we need Running Total value as 0 for ID = 3 which in this case is showing -30.

To reset running total value, we can simply add a case statement and return 0 if there is any negative value. So the script will become this:

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

And the output will be: