# Calculate reverse running total

I'm trying to get rid of using cursors in my project (SQL Server 2012). And I need to prepare query which calculates reverse (with subtraction) running total.

Let me explain on example.

I have the following data set:

``````Id  AppId   CaseId  Amount  Balance
1   2       123     16.48   43.29
2   2       123     5.01    43.29
3   2       123     2.25    43.29
4   4       123     16.48   43.29
5   4       123     5.01    43.29
6   4       123     2.25    43.29
7   10      789     2       10
8   11      789     4       10
``````

I need to get following data set:

``````Id  AppId   CaseId  Amount  Balance Total
1   2       123     16.48   43.29   25.81
2   2       123     5.01    43.29   20.8
3   2       123     2.25    43.29   18.55
4   4       123     16.48   43.29   2.07
5   4       123     5.01    43.29   -2.94
6   4       123     2.25    43.29   -5.19
7   10      789     2       10      8
8   11      789     4       10      4
``````

I've tried to use LAG and SUM() OVER() but I can't understand how to use it correctly.

• what should happen if 2 or more rows with same `caseID` have different `balance`? Mar 24, 2017 at 21:36
• What does "reversive" mean? "reverse" or "recursive"? Mar 24, 2017 at 22:01
• In my case - every single CaseId should have the same Balance. Situation which you've described is impossible. Mar 27, 2017 at 15:03
• "Reversive" - mean opposite, reverse, backward. Sorry for misunderstandings. English is not my native language. Mar 27, 2017 at 15:06

You can use a combination of `MIN() OVER()` and `SUM() OVER()`:

``````SELECT *,
MIN(Balance) OVER(PARTITION BY CaseId) -
SUM(Amount) OVER(PARTITION BY CaseId ORDER BY Id) Total
FROM dbo.YourTable
ORDER BY Id;
``````

Here is a demo of it.

• Many thanks to you! This is exactly what I need. Mar 16, 2017 at 15:30
• Nice. I'm not sure whether `MIN(balance)` is the most appropriate. The OP has to clarify: @whitebeast what should happen if 2 or more rows with same `caseID` have different `balance`? Mar 24, 2017 at 21:35
• And if they are all identical, you could use `FIRST_VALUE(balance) OVER (PARTITION BY CaseId ORDER BY Id) - ...`. I think that would make it a bit more efficient than MIN (due to the same exact window and that MIN has to check all values in a partition before returning the result). Or just `balance - ...` Mar 24, 2017 at 21:58