By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with tbl(person, EvaluationDate, Casescore) as(
select 'Jane', 2012, -12 union all
select 'Hubert', 2014, -5 union all
select 'Jane', 2020, -5 union all
select 'Jane', 2015, +16 union all
select 'Hubert', 2011, -100
), 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
| Person | EvaluationDate | Casescore | CumulativeScore |
|---|---|---|---|
| Hubert | 2011 | -100 | 0 |
| Hubert | 2014 | -5 | 0 |
| Jane | 2012 | -12 | 88 |
| Jane | 2015 | 16 | 100 |
| Jane | 2020 | -5 | 95 |