Query with row by row calculation for running total

Question

I have a problem where jobs become 'due' at the start of a week and each week there are a certain number of 'slots' available to complete any outstanding jobs. If there are not enough slots then the jobs roll over to the next week.

My initial table looks like this:

Week	Slots	Due
23/8/2021	0	1
30/8/2021	2	3
6/9/2021	5	2
13/9/2021	1	4

I want to maintain a running total of the number of 'due' jobs at the end of each week. Each week the number due would be added to the running total from last week, then the number of slots this week would be subtracted. If there are enough slots to do all the jobs required then the running total will be 0 (never negative).

As an example - the below shows how I would achieve this in javascript:

var Total = 0;
data.foreach(function(d){
    Total += d.Due;
    Total -= d.Slots;
    Total = Total > 0 ? Total : 0;
    d.Total = Total;
});

The result would be as below:

Week	Slots	Due	Total
23/8/2021	0	1	1
30/8/2021	2	3	2
6/9/2021	5	2	0
13/9/2021	1	4	3

Is it possible for me to achieve this in SQL (specifically SQL Server 2012)

I have tried various forms of sum(xxx) over (order by yyy)

Closest I managed was:

sum(Due) over (order by Week) - sum(Slots) over (order by Week) as Total

This provided a running total, but will provide a negative total when there are excess slots.

Is the only way to do this with a cursor? If so - any suggestions?

Thanks.

Answer 1

Improvement on previous answer following input from Thorsten

with numbered as (
select *, ROW_NUMBER() OVER (ORDER BY [Week]) as RN
from [Data]
)
,cte as (
select [Week], [Due], [Slots], [RN]
,case when Due > Slots then Due - Slots else 0 end as [Total]
from numbered
where RN = 1

union all

select  e.[Week], e.[Due], e.[Slots], e.[RN]
, case when cte.Total + e.Due - e.Slots > 0 then cte.Total + e.Due - e.Slots else 0 end as [Total]
from numbered e
inner join cte on cte.[RN] = e.[RN] - 1
)

select * from cte

OPTION (MAXRECURSION 0)

Many thanks Thorsten for all your help.