Reset cumulative sum column after threshold with groups

Question

I need to calculate a cumulative sum (group based, column GroupNr) that resets after exceeding some number, in this example - 330.

Can this be done using a function or CTE? If so, how?

Current Table

GroupNr Name    Sum     Cumsum
1       Mary      0.00     0.00
1       Jane    179.00   179.00
1       Tom     106.00   285.00
1       Joseph  175.00   460.00
1       Arthur  253.00   713.00
2       Mary      0.00     0.00
2       Jane    365.00   365.00
2       Tom     365.00   730.00
2       Joseph  365.00  1095.00
2       Arthur  365.00  1460.00

Expected Table

GroupNr Name    Sum     Cumsum  Resetcumsum
1       Mary      0.00    0.00    0.00
1       Jane    179.00  179.00  179.00
1       Tom     106.00  285.00  285.00
1       Joseph  175.00  460.00  460.00 -- Reset point
1       Arthur  253.00  713.00  253.00
2       Mary      0.00    0.00    0.00
2       Jane    365.00  365.00  365.00
2       Tom     365.00  730.00  365.00
2       Joseph  365.00  1095.00 365.00
2       Arthur  365.00  1460.00 365.00

Code for tables

CREATE TABLE Table1 (
    GroupNr int,
    Name varchar(7),
    Sum numeric(14, 2),
    Cumsum numeric(14, 2)
)

INSERT INTO Table1 (GroupNr, Name, Sum, Cumsum)
VALUES (1, 'Mary', 0, 0);
INSERT INTO Table1 (GroupNr, Name, Sum, Cumsum)
VALUES (1, 'Jane', 179, 179);
INSERT INTO Table1 (GroupNr, Name, Sum, Cumsum)
VALUES (1, 'Tom', 106, 285);
INSERT INTO Table1 (GroupNr, Name, Sum, Cumsum)
VALUES (1, 'Joseph', 175, 460);
INSERT INTO Table1 (GroupNr, Name, Sum, Cumsum)
VALUES (1, 'Arthur', 253, 713);
INSERT INTO Table1 (GroupNr, Name, Sum, Cumsum)
VALUES (2, 'Mary', 0, 0);
INSERT INTO Table1 (GroupNr, Name, Sum, Cumsum)
VALUES (2, 'Jane', 365, 365);
INSERT INTO Table1 (GroupNr, Name, Sum, Cumsum)
VALUES (2, 'Tom', 365, 730);
INSERT INTO Table1 (GroupNr, Name, Sum, Cumsum)
VALUES (2, 'Joseph', 365, 1095);
INSERT INTO Table1 (GroupNr, Name, Sum, Cumsum)
VALUES (2, 'Arthur', 365, 1460);

FYI - `VALUES` is plural, i.e. you can define multiple values with `VALUES` for a single `INSERT` - you don't have to repeat the insert for every row. — Dale K, Jun 15 '22 at 21:33
It's actually significantly better for performance too, if you put all the rows in a single `VALUES` clause — Thom A, Jun 15 '22 at 21:40
You need something in your data to use as an order by. In your sample there is nothing here to indicate the order of rows. — Sean Lange, Jun 16 '22 at 04:41

Lukasz Szozda · Accepted Answer · 2022-06-19T17:21:10.430

Capping a cumulative SUM by using standard SUM() OVER() is not possible due to threshold. One way to achieve such result is recursive CTE:

WITH cte_r AS (
  SELECT t.*, ROW_NUMBER() OVER(PARTITION BY GroupNr ORDER BY (SELECT 1)) AS rn 
  FROM Table1 t
), cte AS (
  SELECT GroupNr, Name, [Sum], [CumSum],
        CAST([Sum] AS INT) AS ResetCumSum,
        rn
  FROM cte_r
  WHERE rn = 1
  UNION ALL
  SELECT cte_r.GroupNr, cte_r.Name, cte_r.[Sum], cte_r.[CumSum],
  CAST(CASE WHEN cte.ResetCumSum >= 330 THEN 0 ELSE cte.ResetCumSum END + cte_r.[Sum] AS INT) 
       AS ResetCumSum,
       cte_r.rn
  FROM cte
  JOIN cte_r
    ON cte.rn = cte_r.rn-1
   AND cte.GroupNr = cte_r.GroupNr
)
SELECT GroupNr, Name, [Sum], [CumSum], ResetCumSum
FROM cte
ORDER BY GroupNr, rn;

Output:

db<>fiddle demo

Warning: Table by design is unordered set so to get stable result a order column is required(like unqiue id, timestamp). Here to emulate insert ROW_NUMBER() OVER(PARTITION BY GroupNr ORDER BY (SELECT 1)) AS rn was used but it is not stable.

Extra:

Quirky UPDATE: Running Total until specific condition is true

Disclaimer: "DO NOT USE IT AT PRODUCTION!!!"

-- source table to be extended with id and Resetcumsum  columns
CREATE CLUSTERED INDEX IX_ROW_NUM  ON Table1(GroupNr, id);

DECLARE @running_total       NUMERIC(14,2) = 0
        ,@prev_running_total NUMERIC(14,2) = 0
        ,@prev_GroupNr       INT = 0;

UPDATE Table1
SET 
   @prev_running_total = @running_total
  ,@running_total = Resetcumsum = IIF(@prev_GroupNr != GroupNr 
                                      OR @running_total >= 330, 0, @running_total) 
                                    + [Sum]
  ,@prev_GroupNr = GroupNr
FROM Table1 WITH(INDEX(IX_ROW_NUM))
OPTION (MAXDOP 1);

SELECT *
FROM Table1
ORDER BY id;

db<>fiddle demo - 2

Reset cumulative sum column after threshold with groups

1 Answers1

Extra: