question

Haque avatar image
Haque asked

SQL Server Running Total Reset After A Break

I have a SQL Server 2012 table which has customer number, amount, and a reset field. I’m trying to calculate a running total and also calculate running total after reset. Once the reset flag is set then I want to calculate the running total after the running total. There could be multiple times a reset flag has been set for the same customer. I have the below code but that doesn’t seem to produce the right result. WITH CTEA AS (SELECT * ,SUM(amount) OVER (ORDER BY id) AS RunningTotal FROM #temp) SELECT * ,CASE WHEN resetYN = 1 THEN 0 ELSE Amount + (SELECT LAG(RunningTotal, 1) OVER (ORDER BY id) FROM CTEA S WHERE s.id = ctea.id AND id > 1) END AS ResetRunningTotal1 FROM CTEA ![alt text][1] [1]: /storage/temp/3664-runningtotals.png
sqlsum
runningtotals.png (71.7 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
I would start with a CTE, in which I SUM the ResetYN-flag to create Running Total buckets, and then I'd create a running total in the outer Query, using SUM partitioned by custID and the bucket. declare @t table(id int identity(1,1), custid int, Amount int, resetyn int); INSERT @t(custid, amount, resetyn) values (111,5,0), (112,20,0), (111,6,0), (111,7,1), (111,8,0), (112,10,0), (111,9,0), (111,4,1), (112,50,1), (111,7,0), (112,30,0), (111,9,0), (112,40,0), (112,60,0); WITH CTE AS( SELECT ID, CustID, Amount, ResetYN, SUM(ResetYN) OVER(PARTITION BY CustID ORDER BY ID) as ResetBucket FROM @t )SELECT ID, CustID, Amount, ResetYN, SUM(Amount) OVER(PARTITION BY custID ORDER BY ID) as RunningTotal, SUM(CASE WHEN ResetYN=1 THEN 0 ELSE AMOUNT END) OVER(PARTITION BY CustID, ResetBucket ORDER BY ID) as ResetRunningTotal FROM CTE;
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.