Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
3 added 47 characters in body
source | link
2 added 373 characters in body
source | link
ALTER TABLE #reset_runn_total ADD RunningTotal int;

DECLARE @id int, @val int, @reset int, @acm int, @grp int, @last_grp int;
SET @acm = 0;

DECLARE curRes CURSOR FAST_FORWARD FOR 
SELECT id, val, reset_val, grp
FROM #reset_runn_total
ORDER BY grp, id;

OPEN curRes;
FETCH NEXT FROM curRes INTO @id, @val, @reset;@reset, @grp;
SET @last_grp = @grp;

WHILE @@FETCH_STATUS = 0  
BEGIN
    IF @grp <> @last_grp SET @acm = 0;
    SET @last_grp = @grp;
    SET @acm = @acm + @val;
    UPDATE #reset_runn_total
    SET grpRunningTotal = @acm
    WHERE id = @id;
    IF @acm > @reset SET @acm = 0;
    FETCH NEXT FROM curRes INTO @id, @val, @reset;@reset, @grp;
END

CLOSE curRes;
DEALLOCATE curRes;
 

+----+-----+-----------+-------------+
| id | val | reset_val | grp |RunningTotal|
+----+-----+-----------+-------------+
| 1  | 1   | 10        |     1       |
+----+-----+-----------+-------------+
| 2  | 8   | 12        |     9       |
+----+-----+-----------+-------------+
| 3  | 6   | 14        |     15      |
+----+-----+-----------+-------------+
| 4  | 5   | 10        |     5       |
+----+-----+-----------+-------------+
| 5  | 6   | 13        |     11      |
+----+-----+-----------+-------------+
| 6  | 3   | 11        |     14      |
+----+-----+-----------+-------------+
| 7  | 9   | 8         |     9       |
+----+-----+-----------+-------------+
| 8  | 10  | 12        |     10      |
+----+-----+-----------+-------------+
DECLARE @id int, @val int, @reset int, @acm int;
SET @acm = 0;

DECLARE curRes CURSOR FAST_FORWARD FOR 
SELECT id, val, reset_val
FROM #reset_runn_total
ORDER BY id;

OPEN curRes;
FETCH NEXT FROM curRes INTO @id, @val, @reset;

WHILE @@FETCH_STATUS = 0  
BEGIN
    SET @acm = @acm + @val;
    UPDATE #reset_runn_total
    SET grp = @acm
    WHERE id = @id;
    IF @acm > @reset SET @acm = 0;
    FETCH NEXT FROM curRes INTO @id, @val, @reset;
END

CLOSE curRes;
DEALLOCATE curRes;
 

+----+-----+-----------+-----+
| id | val | reset_val | grp |
+----+-----+-----------+-----+
| 1  | 1   | 10        | 1   |
+----+-----+-----------+-----+
| 2  | 8   | 12        | 9   |
+----+-----+-----------+-----+
| 3  | 6   | 14        | 15  |
+----+-----+-----------+-----+
| 4  | 5   | 10        | 5   |
+----+-----+-----------+-----+
| 5  | 6   | 13        | 11  |
+----+-----+-----------+-----+
| 6  | 3   | 11        | 14  |
+----+-----+-----------+-----+
| 7  | 9   | 8         | 9   |
+----+-----+-----------+-----+
| 8  | 10  | 12        | 10  |
+----+-----+-----------+-----+
ALTER TABLE #reset_runn_total ADD RunningTotal int;

DECLARE @id int, @val int, @reset int, @acm int, @grp int, @last_grp int;
SET @acm = 0;

DECLARE curRes CURSOR FAST_FORWARD FOR 
SELECT id, val, reset_val, grp
FROM #reset_runn_total
ORDER BY grp, id;

OPEN curRes;
FETCH NEXT FROM curRes INTO @id, @val, @reset, @grp;
SET @last_grp = @grp;

WHILE @@FETCH_STATUS = 0  
BEGIN
    IF @grp <> @last_grp SET @acm = 0;
    SET @last_grp = @grp;
    SET @acm = @acm + @val;
    UPDATE #reset_runn_total
    SET RunningTotal = @acm
    WHERE id = @id;
    IF @acm > @reset SET @acm = 0;
    FETCH NEXT FROM curRes INTO @id, @val, @reset, @grp;
END

CLOSE curRes;
DEALLOCATE curRes;

+----+-----+-----------+-------------+
| id | val | reset_val | RunningTotal|
+----+-----+-----------+-------------+
| 1  | 1   | 10        |     1       |
+----+-----+-----------+-------------+
| 2  | 8   | 12        |     9       |
+----+-----+-----------+-------------+
| 3  | 6   | 14        |     15      |
+----+-----+-----------+-------------+
| 4  | 5   | 10        |     5       |
+----+-----+-----------+-------------+
| 5  | 6   | 13        |     11      |
+----+-----+-----------+-------------+
| 6  | 3   | 11        |     14      |
+----+-----+-----------+-------------+
| 7  | 9   | 8         |     9       |
+----+-----+-----------+-------------+
| 8  | 10  | 12        |     10      |
+----+-----+-----------+-------------+
1
source | link

Using a CURSOR:

DECLARE @id int, @val int, @reset int, @acm int;
SET @acm = 0;

DECLARE curRes CURSOR FAST_FORWARD FOR 
SELECT id, val, reset_val
FROM #reset_runn_total
ORDER BY id;

OPEN curRes;
FETCH NEXT FROM curRes INTO @id, @val, @reset;

WHILE @@FETCH_STATUS = 0  
BEGIN
    SET @acm = @acm + @val;
    UPDATE #reset_runn_total
    SET grp = @acm
    WHERE id = @id;
    IF @acm > @reset SET @acm = 0;
    FETCH NEXT FROM curRes INTO @id, @val, @reset;
END

CLOSE curRes;
DEALLOCATE curRes;


+----+-----+-----------+-----+
| id | val | reset_val | grp |
+----+-----+-----------+-----+
| 1  | 1   | 10        | 1   |
+----+-----+-----------+-----+
| 2  | 8   | 12        | 9   |
+----+-----+-----------+-----+
| 3  | 6   | 14        | 15  |
+----+-----+-----------+-----+
| 4  | 5   | 10        | 5   |
+----+-----+-----------+-----+
| 5  | 6   | 13        | 11  |
+----+-----+-----------+-----+
| 6  | 3   | 11        | 14  |
+----+-----+-----------+-----+
| 7  | 9   | 8         | 9   |
+----+-----+-----------+-----+
| 8  | 10  | 12        | 10  |
+----+-----+-----------+-----+