Reset Running Total based on another column

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 

You can try using a quirky update like this

--- setupIF OBJECT_ID('tempdb..#reset_runn_total') IS NOT NULL DROP TABLE #reset_runn_totalcreate 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 updateDECLARE     @running_sum    INT            , @temp         INTUPDATE      #reset_runn_totalSET         @temp = running_sum = COALESCE(@running_sum, 0) + val            , @running_sum = CASE WHEN @temp < reset_val THEN @temp ELSE 0 ENDOPTION      (FORCE ORDER)--- dump resultSELECT * 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.