SQL: How do I calculate the earliest date that a material will be needed using a recursive CTE?

Question

I know I can go the long way and do this with a while loop, but in general these run more slowly in SQL Server and so I wanted to see if this can be done using a recursive common table expression. Below is the data I'm working with.

--#Inventory
Material    QtyOnHand
Matl_A      50


--#ToStockOrders --jobs that fill our inventory
Job      ItemProduced    StockQty   DueDate
Job_1    Matl_A          25         ???
Job_2    Matl_A          50         ???
Job_3    Matl_A          25         ???


--#CustomerDemand
DueDate    Job      MatlQty   BillOfMaterials
1-1-23     Job_A    100       Matl_A
2-1-23     Job_B    100       Matl_A
3-1-23     Job_C    100       Matl_A

I need to calculate the due date column for each job in #ToStockOrders. This value comes from a calculation. The code will need to step through as follows:

1. For the first job in #CustomerDemand (Job_A), it requires 100 of Matl_A. We have 50 in inventory, which means that job will need 50 after we use that up.
2. The first job in #ToStockOrders is producing 25, which will go to fill that remaining need of 50 for Job_A. Thus, we write 1-1-23 as the due date for Job_1 in the #ToStockOrders table.
3. Job_A still needs 25 more, so now we look at the second row of #ToStockOrders. This job is producing 50, so half of that will fill the remaining need. We write '1-1-23' here as the due date.
4. Now we go to the second row of #CustomerDemand since we finished with the first row. Job_B also requires 100 of Matl_A. We have 25 left over from Job_2, so we use that to partially fill that demand. We do not update the due date here because we want this value to be the earliest that the produced materials are needed. Remaining demand to be fill for Job_B is now 75.
5. Now we go to the third row of #ToStockOrders. This job is producing 25. We use that to partially fill the demand for Job_B and write a due date of '2-1-23' for its row in #ToStockOrders. Now we are done because we filled a due date for each row in #ToStockOrders.

Answer 1

My suggestion (as an approach that works reasonably) is to use running totals of demand for materials vs the amount you have available, and use the dates based on comparing these totals.

You can follow along at this db<>fiddle.

Note - I assume that the stock orders are ordered e.g., you will get Job_1 before you get Job_2. If you can change ordering of orders to help match demand, that becomes a quite different problem.

For ease of use, I have separated out the steps to make it clear what each one does, and used #TemporaryTables to record the data. You can combine steps if desired.

To start with, I created two tables #CustDemandByDate, and #StockByJob, which are similar to your original tables. However, they have a few tweaks with a few tweaks

• The first table has total customer demand by due date - so that if two customers each want 100 units of Matl_A on the same date, then you need 200 on that date (specific jobs are removed)
• The second table has your stock availabilities - including a sort field (Auto_ID) and including your existing stock as the original value
• Both tables have a 'running total' of stock - the total_required representing demand, and total_available representing your stock.

-- #CustDemandByDate
Material    DueDate                 QtyRequired Total_Required
Matl_A      2023-01-01              100         100
Matl_A      2023-01-02              100         200
Matl_A      2023-01-03              100         300

-- #StockByJob
Material    Auto_ID Job             StockQty    Total_Available
Matl_A      0       Current stock   50          50
Matl_A      1       Job_1           25          75
Matl_A      2       Job_2           50          125
Matl_A      3       Job_3           25          150

Having calculated the above tables, the only real step is to compare the running totals:

• For each row in #StockByJob (e.g., your available stock)...
• Work out when the earliest date at which the total_required of that material is on or after the total available

This is done below with a OUTER APPLY (see here and here - this is similar to an OUTER JOIN but instead does a 'calculation' for each row rather than looking up the value in a table).

SELECT      #StockByJob.Material,
            #StockByJob.Job,
            #StockByJob.StockQty,
            CustDemandDates.DueDate AS Date_AllUsed
    FROM    #StockByJob
            OUTER APPLY
                (SELECT TOP 1 *
                    FROM    #CustDemandByDate
                    WHERE   #CustDemandByDate.Material = #StockByJob.Material
                            AND #CustDemandByDate.Total_Required >= #StockByJob.Total_Available
                    ORDER BY DueDate
                ) AS CustDemandDates
    ORDER BY Material, Auto_ID;

Results as follows

Material    Job             StockQty    Date_AllUsed
Matl_A      Current stock   50          2023-01-01
Matl_A      Job_1           25          2023-01-01
Matl_A      Job_2           50          2023-01-02
Matl_A      Job_3           25          2023-01-02

The final date is labelled 'Date_AllUsed' - this represents the date that that production run has been all used up/provided to customers.

---

Calculating running totals

Note - calculating running totals is done with the SUM() function using the OVER clause. See below for the commands used.

-- Calculate #CustDemandByDate
CREATE TABLE #CustDemandByDate (Material nvarchar(20), DueDate date, QtyRequired int, Total_Required int, PRIMARY KEY (Material, DueDate));
INSERT INTO #CustDemandByDate (Material, DueDate, QtyRequired)
    SELECT  BillOfMaterials AS Material, DueDate, SUM(MatlQty) AS QtyRequired
    FROM    #CustomerDemand
    GROUP BY BillOfMaterials, DueDate;

WITH RunningTotal AS
        (SELECT     Material, DueDate, Total_Required,
                    SUM(QtyRequired) OVER (PARTITION BY Material ORDER BY DueDate) AS Total_Required_Calc
            FROM    #CustDemandByDate
        )
    UPDATE  RunningTotal
        SET Total_Required = Total_Required_Calc;

-- Calculate #StockByJob
CREATE TABLE #StockByJob (Material nvarchar(20), Auto_ID int, Job nvarchar(20), StockQty int, Total_Available int, PRIMARY KEY (Material, Auto_ID));
INSERT INTO #StockByJob (Material, Auto_ID, Job, StockQty)
    SELECT  ItemProduced AS Material, Auto_ID, Job, StockQty
    FROM    #ToStockOrders
      UNION ALL
    SELECT  Material, 0, 'Current stock', QtyOnHand
    FROM    #Inventory;

WITH RunningTotal AS
        (SELECT     Material, Auto_ID, Total_Available,
                    SUM(StockQty) OVER (PARTITION BY Material ORDER BY Auto_ID) AS Total_Available_Calc
            FROM    #StockByJob
        )
    UPDATE  RunningTotal
        SET Total_Available = Total_Available_Calc;

---

Update for SQL Server 2008 - Calculating running totals

I do not have access to SQL Server 2008, so I cannot test this, but I believe the SQL Server 2008 cannot use SUM() OVER () to calculate running totals. Therefore another method is required.

I have put an approach here based on Tim Biegeleisen's answer in How to calculate the running total of a column in SQL Server 2008 R2? but also note that it is quite inefficient as per Aaron Bertrand's answer in Calculate running total / running balance .

From the section above - the calculating running totals section - we still want to create the same tables #CustDemandByDate and #StockByJob. However, to populate the data and calculate running totals, we will use different commands that should work in 2008. An updated db<>fiddle includes the new commands.

WITH TotDemandByDate AS
        (SELECT BillOfMaterials AS Material, DueDate, SUM(MatlQty) AS QtyRequired
            FROM    #CustomerDemand
            GROUP BY BillOfMaterials, DueDate
        )
    INSERT INTO #CustDemandByDate (Material, DueDate, QtyRequired, Total_Required)
        SELECT  T1.Material, 
                T1.DueDate, 
                T1.QtyRequired,
                SUM(T2.QtyRequired) AS Total_Required
        FROM    TotDemandByDate AS T1
                INNER JOIN TotDemandByDate AS T2 ON T1.Material = T2.Material AND T1.DueDate >= T2.DueDate
        GROUP BY T1.Material, T1.DueDate, T1.QtyRequired;


WITH SupplyByDate AS
        (SELECT  ItemProduced AS Material, Auto_ID, Job, StockQty
        FROM    #ToStockOrders
            UNION ALL
        SELECT  Material, 0, 'Current stock', QtyOnHand
        FROM    #Inventory
        )
    INSERT INTO #StockByJob (Material, Auto_ID, Job, StockQty, Total_Available)
        SELECT  T1.Material, 
                T1.Auto_ID, 
                T1.Job,
                T1.StockQty,
                SUM(T2.StockQty) AS Total_Required
        FROM    SupplyByDate AS T1
                INNER JOIN SupplyByDate AS T2 ON T1.Material = T2.Material AND T1.Auto_ID >= T2.Auto_ID
        GROUP BY T1.Material, T1.Auto_ID, T1.Job, T1.StockQty;