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.

"With" a cursor? or "without" a cursor? You should aim to use set based operations wherever possible. — Dale K, Aug 23 '21 at 07:29
You will have to use some sort of recursion. Thorsten mentions recursive query - fine, I find its syntax a bit alien. Cursor is another choice, too much complex syntax. I'd recommend a `while` statement. — George Menoutis, Aug 23 '21 at 07:54
rounning total culculation = due - slots. If value negative then it consider due as running total colculation or any logic behind this. please explain. — Rahul Biswas, Aug 23 '21 at 08:06
@Rahul Biswas: I think JHW has explained this alright. The due 1 on 23/8/2021 cannot be met, as the slot is 0. So it adds to the due 3 on 30/8/2021. That makes 4, but there are just 2 slots. So on 6/9/2021 the remaining 2 get added to the date's 2 due, which makes 4. There are 5 slots, so nothing remains. This is an iterative process, because you cannot just add up dues and slots. With 1 due, it makes no difference whether you have 1 slot or 1000. You need a loop. And a loop in SQL means recursive query. — Thorsten Kettner, Aug 23 '21 at 08:40
Sorry @DaleK - I was not very clear. I was hoping to avoid using a cursor (as have always read not to) but was wondering if that was the only way. — JHW, Aug 23 '21 at 10:35
@ThorstenKettner - thank you for suggesting a recursive query. Am I right in thinking I will need to set MAXRECURSION to at least the maximum number of rows I will be dealing with? I am going to edit my question with my thoughts - hope that is ok... — JHW, Aug 23 '21 at 10:37

JHW · Answer 1 · 2021-08-23T11:01:50.467

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 :-)

The recursive query looks good. You are right about that week thing, though, and that you should rather work on numbers (a non-recursive cte numbering the rows as a first step, then base the recursive cte on that result). As to MAXRECURSION´: set this to 0, which means you don't need the DBMS to watch out for a limit. I must admit, I don't even know why MAXRECURSION exists in SQL Server at all. — Thorsten Kettner, Aug 23 '21 at 11:11

score 1 · Accepted Answer · answered Aug 23 '21 at 11:31

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.

Query with row by row calculation for running total

2 Answers2