Sql-server – Calculate reverse running total

sql serversql-server-2012

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.

Could you please help me with it?

Best Answer

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.

Related Question