1

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?

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

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.

|improve this answer|||||
  • Many thanks to you! This is exactly what I need. – whitebeast Mar 16 '17 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? – ypercubeᵀᴹ Mar 24 '17 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 - ... – ypercubeᵀᴹ Mar 24 '17 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.