tSQL:如何使用递归CTE计算材料需要的最早日期?

Question

我知道我可以走很长的路，用while循环做到这一点，但一般来说，这些在SQL Server中运行得更慢，所以我想看看这是否可以使用递归的公共表表达式来完成。下面是我正在处理的数据。

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

我需要计算#ToStockOrders中每个作业的到期日列。这个值来自于计算。代码需要按如下步骤执行:

1. 对于#CustomerDemand(Job_A)中的第一个作业，需要100个Matl_A。我们有50个库存，这意味着我们用完后需要50个。
#ToStockOrders中的第一个任务是生产25个，这将填补Job_A剩余的50个需求。因此，我们将1-1-23写入#ToStockOrders表中Job_1的到期日。
2. Job_A还需要25个，所以现在我们看#ToStockOrders的第二行。这个工作生产50个，所以剩下的一半可以满足需求。我们在这里写"1-1-23"作为截止日期。
3. 现在我们进入#CustomerDemand的第二行，因为我们完成了第一行。Job_B也需要100个Matl_A。Job_2还剩下25个，所以我们用它来部分满足需求。我们没有在这里更新截止日期，因为我们希望这个值是需要生产材料的最早时间。Job_B的剩余需求现在是75。
4. 现在我们转到#ToStockOrders的第三行。这个工作产出25。我们使用它来部分地满足Job_B的需求，并为其在#ToStockOrders中的行写入"2-1-23"的到期日。现在我们完成了，因为我们为#ToStockOrders中的每一行填写了截止日期。

Answer 1

我的建议(作为一种合理的方法)是使用对材料需求的运行总数与您可用的数量，并在比较这些总数的基础上使用日期。

您可以在此数据库<>提琴上进行操作。

注意-我假设库存订单是订购的，例如，您将在获得Job_2之前获得Job_1。如果你可以改变订单的顺序来帮助满足需求，那就变成一个完全不同的问题了。

为了便于使用，我将这些步骤分开，以清楚地说明每个步骤的作用，并使用#TemporaryTables来记录数据。如果需要，可以组合步骤。

首先，我创建了两个表#CustDemandByDate和#StockByJob，它们与原始表类似。然而，他们有一些调整通过一些调整

• 第一个表包含截止日期前的总客户需求-因此，如果两个客户在同一日期各自需要100个Matl_A单位，那么在该日期您需要200个(特定作业被删除)
• 第二个表有您的库存可用性-包括一个排序字段(Auto_ID)，并包括您现有的库存作为原始值
• 两个表都有一个库存的"运行总数"——total_required代表需求，total_available代表库存。

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

计算完上面的表后，唯一真正的步骤是比较运行总数:

• 对于#StockByJob中的每一行(例如，您的可用库存)…
• 计算出该物料的总需要量在总可用量上或之后的最早日期

这是用下面的OUTER APPLY完成的(参见这里和这里-这类似于OUTER JOIN，但它对每一行进行"计算"，而不是在表中查找值)。

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

最终日期标记为'Date_AllUsed' -这表示该生产运行已全部用完/提供给客户的日期。

---

计算总跑数

注意-计算运行总数是通过使用OVER子句的SUM()函数完成的。下面是使用的命令。

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

---

SQL Server 2008的更新-计算运行总数

我没有访问SQL Server 2008，所以我不能测试这个，但我相信SQL Server 2008不能使用SUM() OVER ()来计算运行总数。因此，需要另一种方法。

我已经把一个方法在这里基于Tim Biegeleisen的回答如何计算一个列的运行总数在SQL Server 2008 R2?但也要注意，根据Aaron Bertrand在计算运行总数/运行平衡中的回答，这是相当低效的。

在上面计算运行总数的部分中，我们仍然希望创建相同的表#CustDemandByDate和#StockByJob。但是，为了填充数据并计算运行总数，我们将使用在2008年应该可以使用的不同命令。更新后的数据库<>fiddle包含了新的命令。

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;