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.

Answer 2

Possible answer(s) to my own question based on suggestions in comments.

Thorsten Kettner suggested a recursive query:

with cte as (

select [Week], [Due], [Slots]
,case when Due > Slots then Due - Slots else 0 end as [Total]
from [Data]
where [Week] = (select top 1 [Week] from [Data])

union all

select  e.[Week], e.[Due], e.[Slots]
, case when cte.Total + e.Due - e.Slots > 0 then cte.Total + e.Due - e.Slots else 0 end as [Total]
from [Data] e
inner join cte on cte.[Week] = dateadd(day,-7,e.[Week])
)

select * from cte

OPTION (MAXRECURSION 200)

Thorsten - is this what you were suggesting? (If you have any improvements, please post as an answer so I can accept it!)

Presumably I have to ensure that MAXRECURSION is set to something higher than the number of rows I will be dealing with?

I am a little bit nervous about the join on dateadd(day,-7,e.[Week]). Would I be better doing something with Row_Number() to get the previous record? I may want to use something other than weeks, or weeks may be missing?

George Menoutis suggested a 'while' query and I was looking for ways to implement that when I came across this post: https://stackoverflow.com/a/35471328/1372848

This suggested that a cursor may not be all that bad compared to a while?

This is the cursor based version I came up with:

SET NOCOUNT ON;
DECLARE @Week Date,
        @Due Int,
        @Slots Int,
        @Total Int = 0;

DECLARE @Output TABLE ([Week] Date NOT NULL, Due Int NOT NULL, Slots Int NOT NULL, Total Int);

DECLARE crs CURSOR STATIC LOCAL READ_ONLY FORWARD_ONLY
FOR  SELECT [Week], Due, Slots
     FROM   [Data]
    ORDER BY [Week] ASC;

OPEN crs;

FETCH NEXT
FROM  crs
INTO  @Week, @Due, @Slots;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    Set @Total = @Total + @Due;
    Set @Total = @Total - @Slots;
    Set @Total = IIF(@Total > 0, @Total , 0)

    INSERT INTO @Output ([Week], [Due], [Slots], [Total])
    VALUES (@Week, @Due, @Slots, @Total);

    FETCH NEXT
    FROM  crs
    INTO  @Week, @Due, @Slots;
END;

CLOSE crs;
DEALLOCATE crs;

SELECT *
FROM   @Output;

Both of these seem to work as intended. The recursive query feels better (cursors = bad etc), but is it designed to be used this way (with a recursion for every input row and therefore potentially a very high number of recursions?)

Many thanks for everyone's input :-)