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