1

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.

enter image description here

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
4

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. – user20907 Sep 9 '16 at 4:06

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.