SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Initial data creation
CREATE TABLE #Inventory (Material nvarchar(20) PRIMARY KEY, QtyOnHand int);
INSERT INTO #Inventory (Material, QtyOnHand) VALUES
(N'Matl_A', 50);

CREATE TABLE #ToStockOrders (Auto_ID int IDENTITY(1,1), Job nvarchar(20) PRIMARY KEY, ItemProduced nvarchar(20), StockQty int);
INSERT INTO #ToStockOrders (Job, ItemProduced, StockQty) VALUES
(N'Job_1', N'Matl_A', 25),
(N'Job_2', N'Matl_A', 50),
(N'Job_3', N'Matl_A', 25);

CREATE TABLE #CustomerDemand (DueDate date, Job nvarchar(20) PRIMARY KEY, MatlQty int, BillOfMaterials nvarchar(20));
INSERT INTO #CustomerDemand (DueDate, Job, MatlQty, BillOfMaterials) VALUES
('20230101', N'Job_A', 100, N'Matl_A'),
('20230102', N'Job_B', 100, N'Matl_A'),
('20230103', N'Job_C', 100, N'Matl_A');
7 rows affected

-- Tables used for calculations 
CREATE TABLE #CustDemandByDate (Material nvarchar(20), DueDate date, QtyRequired int, Total_Required int, PRIMARY KEY (Material, DueDate));
CREATE TABLE #StockByJob (Material nvarchar(20), Auto_ID int, Job nvarchar(20), StockQty int, Total_Available int, PRIMARY KEY (Material, Auto_ID));
-- (SQL Server 2012 and up) Calculate #CustDemandByDate and #StockByJob
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;

SELECT * FROM #CustDemandByDate ORDER BY Material, DueDate;

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

SELECT * FROM #StockByJob ORDER BY Material, Auto_ID;


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




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


-- (SQL Server 2008) Calculate #CustDemandByDate and #StockByJob
-- First empty tables filled in previous block
TRUNCATE TABLE #CustDemandByDate;
TRUNCATE TABLE #StockByJob;
----
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;


SELECT * FROM #CustDemandByDate ORDER BY Material, DueDate;
----
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;

SELECT * FROM #StockByJob ORDER BY Material, Auto_ID;


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




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


-- Final calculation
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;


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