SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

INSERT INTO Table1 (id, GroupNr, Name, Sum, Cumsum)
VALUES (1,1, 'Mary', 0, 0);
INSERT INTO Table1 (id, GroupNr, Name, Sum, Cumsum)
VALUES (2,1, 'Jane', 179, 179);
INSERT INTO Table1 (id, GroupNr, Name, Sum, Cumsum)
VALUES (3,1, 'Tom', 106, 285);
INSERT INTO Table1 (id, GroupNr, Name, Sum, Cumsum)
VALUES (4,1, 'Joseph', 175, 460);
INSERT INTO Table1 (id, GroupNr, Name, Sum, Cumsum)
VALUES (5,1, 'Arthur', 253, 713);
INSERT INTO Table1 (id, GroupNr, Name, Sum, Cumsum)
VALUES (6,2, 'Mary', 0, 0);
INSERT INTO Table1 (id, GroupNr, Name, Sum, Cumsum)
VALUES (7,2, 'Jane', 365, 365);
INSERT INTO Table1 (id, GroupNr, Name, Sum, Cumsum)
VALUES (8,2, 'Tom', 365, 730);
INSERT INTO Table1 (id, GroupNr, Name, Sum, Cumsum)
VALUES (9,2, 'Joseph', 365, 1095);
INSERT INTO Table1 (id, GroupNr, Name, Sum, Cumsum)
VALUES (10, 2, 'Arthur', 365, 1460);
10 rows affected

CREATE CLUSTERED INDEX IX_ROW_NUM  ON Table1(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;


id
GroupNr
Name
Sum
Cumsum
Resetcumsum


1
1
Mary
0.00
0.00
0.00

2
1
Jane
179.00
179.00
179.00

3
1
Tom
106.00
285.00
285.00

4
1
Joseph
175.00
460.00
460.00

5
1
Arthur
253.00
713.00
253.00

6
2
Mary
0.00
0.00
0.00

7
2
Jane
365.00
365.00
365.00

8
2
Tom
365.00
730.00
365.00

9
2
Joseph
365.00
1095.00
365.00

10
2
Arthur
365.00
1460.00
365.00


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;


GroupNr
Name
Sum
CumSum
ResetCumSum


1
Mary
0.00
0.00
0

1
Jane
179.00
179.00
179

1
Tom
106.00
285.00
285

1
Joseph
175.00
460.00
460

1
Arthur
253.00
713.00
253

2
Mary
0.00
0.00
0

2
Jane
365.00
365.00
365

2
Tom
365.00
730.00
365

2
Joseph
365.00
1095.00
365

2
Arthur
365.00
1460.00
365

id	GroupNr	Name	Sum	Cumsum	Resetcumsum
1	1	Mary	0.00	0.00	0.00
2	1	Jane	179.00	179.00	179.00
3	1	Tom	106.00	285.00	285.00
4	1	Joseph	175.00	460.00	460.00
5	1	Arthur	253.00	713.00	253.00
6	2	Mary	0.00	0.00	0.00
7	2	Jane	365.00	365.00	365.00
8	2	Tom	365.00	730.00	365.00
9	2	Joseph	365.00	1095.00	365.00
10	2	Arthur	365.00	1460.00	365.00