Reset Running Total based on another column

You can try using a quirky update like this

--- setup
IF OBJECT_ID('tempdb..#reset_runn_total') IS NOT NULL DROP TABLE #reset_runn_total
create table #reset_runn_total(id int identity(1,1) PRIMARY KEY, val int, reset_val int, running_sum int)

insert into #reset_runn_total(val, reset_val) values (1,10),(8,12),(6,14),(5,10),(6,13),(3,11),(9,8),(10,12)

--- use quirky update
DECLARE     @running_sum    INT
            , @temp         INT

UPDATE      #reset_runn_total
SET         @temp = running_sum = COALESCE(@running_sum, 0) + val
            , @running_sum = CASE WHEN @temp < reset_val THEN @temp ELSE 0 END
OPTION      (FORCE ORDER)

--- dump result
SELECT * FROM #reset_runn_total

Note that CLUSTERED INDEX on the temp table is required (PK's default type) for OPTION (FORCE ORDER) to make sense.


Try flag previous row

WITH cte
     AS (SELECT id,
                val,
                reset_val,
                val AS running_total,
                CASE WHEN val > reset_val THEN 1 ELSE 0 END as flag
         FROM   #reset_runn_total
         WHERE  id = 1
         UNION ALL
         SELECT r.*,
                CASE c.flag
                  WHEN 1 then r.val
                  ELSE c.running_total + r.val
                END,
                CASE WHEN CASE c.flag
                      WHEN 1 then r.val
                      ELSE c.running_total + r.val
                    END > r.reset_val 
                    THEN 1 ELSE 0 END
         FROM   cte c
                JOIN #reset_runn_total r
                  ON r.id = c.id + 1)
SELECT *
FROM   cte