SQL Server cumulative sum with restricted range

Question

Given a table of people and the score associated to their evaluation, I need to get their cumulative score (by person) for each evaluation.

Person	EvaluationDate	Score
Jane	2012	-12
Hubert	2014	-5
Jane	2020	-5
Jane	2015	+16
Hubert	2011	-100

Their scores start from 100 and cannot go below 0 or beyond 100. If at a certain time the score goes beyond 100 (or below 0), the next cumulative score is calculated from 100 (or 0).

Expected output :

Person	EvaluationDate	Score	CumulativeScore
Hubert	2011	-100	0
Hubert	2014	-5	0
Jane	2012	-12	88
Jane	2015	+16	100
Jane	2020	-5	95

There are posts describing how to do a cumulative sum but they do not explain how to restrict it to a range through each operation.

It looks like you are searching for [Capping a running total](https://stackoverflow.com/a/52936314/5070879). The possible approaches for SQL Server: recursive CTE or cursor/while loop(not set-based solutions) or [quirky update(Do not use on production)](https://stackoverflow.com/a/33071417/5070879) — Lukasz Szozda, Jun 21 '21 at 08:59

Serg · Accepted Answer · 2021-06-21T11:42:47.910

As @LukaszSzozda advised in his comment you can use a recursive CTE.

with t as (
   select Person, EvaluationDate, Casescore,
       row_number() over(partition by Person order by EvaluationDate) rn
   from tbl 
), rq as(
   select *,
       case when 100 + Casescore < 0   then 0
            when 100 + Casescore > 100 then 100
            else 100 + Casescore end CumulativeScore
   from t
   where rn = 1
   
   union all 
   
   select t.Person, t.EvaluationDate, t.Casescore, t.rn,
       case when rq.CumulativeScore + t.Casescore < 0   then 0
            when rq.CumulativeScore + t.Casescore > 100 then 100
            else rq.CumulativeScore + t.Casescore end 
   from rq
   join t on t.rn = rq.rn + 1 and t.Person = rq.Person
)
select Person, EvaluationDate, Casescore, CumulativeScore
from rq
order by Person, EvaluationDate;

db<>fiddle

Thanks ! When handling many evaluations a recursion error is thrown, appending `option (maxrecursion 0)` at the end of the query solves the issue — Noan Cloarec, Jun 21 '21 at 13:52

SQL Server cumulative sum with restricted range

1 Answers1