Introduction

The problem presented in the Stack Overflow post involves calculating the running total of “due” jobs at the end of each week, given certain constraints. The goal is to determine if it is possible to achieve this in SQL Server 2012 using various methods, including recursive queries and cursors.

Understanding the Problem

To understand the problem better, let’s break down the requirements:

• Calculate the running total of “due” jobs at the end of each week.
• Each week, add the number of “due” jobs to the running total from last week.
• Subtract the number of available slots from the current week from the running total.
• If there are enough slots to complete all outstanding jobs, the running total should be 0 (never negative).

Initial Attempts

The initial attempt using SQL Server’s built-in functions did not yield the desired result, as it provided a running total but with a potential negative value when there were excess slots. This led to exploring alternative methods.

Recursive Queries

One suggested approach was using recursive queries, which involve querying the same table multiple times with different conditions. Thorsten Kettner’s suggestion used a Common Table Expression (CTE) to achieve this:

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

Recursive Query Considerations

• MAXRECURSION: To ensure the recursive query works correctly, it’s essential to set MAXRECURSION to a value higher than the expected number of rows in the result set.
• Join Condition: The join condition on cte.[Week] = DATEADD(DAY, -7, e.[Week]) might not always work as intended, especially when weeks are missing or have gaps. Using Row_Number() or similar functions could provide a more reliable solution.

Cursor-Based Approach

George Menoutis suggested using a while query to achieve the desired result. The cursor-based approach looked like this:

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;

Comparison and Conclusion

• Recursive queries seem to be a more efficient approach than using cursors, with fewer potential issues related to indexing and performance.
• The recursive query needs to ensure that MAXRECURSION is set correctly to handle the expected number of rows in the result set.
• Using Row_Number() or similar functions might provide a more reliable solution when dealing with weeks having gaps or missing values.

Ultimately, both approaches seem viable, but it’s essential to weigh the trade-offs and choose the one that best fits your specific requirements.

Last modified on 2024-11-26