# Calculating Running Total After Reset

I have a SQL Server 2012 table which has customer id, amount, and a reset columns. I’m trying to calculate the running total and also calculate the running total after reset. Once the reset "flag" is set then I want to calculate the running total after the running total. I have the following code and not sure if there's a way to calculate running total after a break point.

``````WITH a
AS
(SELECT
*
,SUM(amount) OVER (ORDER BY id) AS RunningTotal
FROM Table1)
SELECT
*
,CASE
WHEN resetYN = 1 THEN 0
ELSE Amount + LAG(RunningTotal, 1) OVER (ORDER BY id)
END AS ResetRunningTotal
FROM a
``````

Here's one method of getting running totals after reset. There are probably others out there. Here is your test data:

``````CREATE TABLE user20907 (
ID INTEGER NULL,
CUSTOMERID INTEGER NULL,
AMOUNT INTEGER NULL,
RESETYN INTEGER NULL
);

BEGIN TRANSACTION;
INSERT INTO user20907 VALUES (1,111,5,0);
INSERT INTO user20907 VALUES (2,111,6,0);
INSERT INTO user20907 VALUES (3,111,7,1);
INSERT INTO user20907 VALUES (4,111,8,0);
INSERT INTO user20907 VALUES (5,111,9,0);
INSERT INTO user20907 VALUES (6,111,4,1);
INSERT INTO user20907 VALUES (7,111,7,0);
INSERT INTO user20907 VALUES (8,111,9,0);
COMMIT TRANSACTION;
``````

First get a running total of the `ResetYN` column. I'm going to call it `SUM_OF_RESETYN`. In an outer query you can partition by both `CustomerID` and `SUM_OF_RESETYN`. For the reset running total, instead of taking `Amount` directly you can do a case statement similar to what you already have:

``````SELECT
t.ID
, CUSTOMERID
, AMOUNT
, RESETYN
, RUNNINGTOTAL
, SUM(CASE WHEN RESETYN = 1 THEN 0 ELSE AMOUNT END) OVER (PARTITION BY CUSTOMERID, SUM_OF_RESETYN ORDER BY ID) RESETRUNNINGTOTAL
FROM
(
SELECT
ID
, CUSTOMERID
, AMOUNT
, RESETYN
, SUM(AMOUNT) OVER (PARTITION BY CUSTOMERID ORDER BY ID) RUNNINGTOTAL
, SUM(RESETYN) OVER (PARTITION BY CUSTOMERID ORDER BY ID) SUM_OF_RESETYN
FROM user20907
) t;
``````

I ran that query and the results matched yours, except that I did not have a NULL value for `RESETRUNNINGTOTAL` for `ID` 1. Was that intentional? If so it should be easy to integrate that bit of logic into my query.

• Thanks for the reply. Getting a running total of the ResetYN and then partitioning it by that will work if there's only one record with ResetYN=1. There will be case when the same customer id will be multiple ResetFlag. I know the example above has only one record with ResetFlag of 1 but there would be cases when there are four records that has ResetFlag of 1. Sep 9, 2016 at 4:06