SQL Server 2018中根据FIFO逻辑的行配对

Question

我需要使用FIFO(先进先出)逻辑将销售订单与采购订单配对。

在第一个表中，我将销售订单按升序排列(SO_RowId和SO_CreationDate列告诉我们有关订单的信息)。

在第二个表中，我的采购订单也是按升序排列的(PO_RowId和PO_CreationDate列告诉我们订单的信息)。

第三个表(FINAL table)表示配对结果，即我想要得到的结果，但我不知道如何接近它。

表1(销售订单)

SO_CreationDate2022-01-012022-03-032022-03-032022-04-012022-05-01

实现此目的的一种方法是获取每个表的当前运行总数和以前运行总数，然后将逻辑构建到case表达式中。

例如,

DECLARE @Table1 TABLE (
SO_RowId int,
SO_Quantity decimal(15,4),
SO_CreationDate datetime
);
INSERT @Table1 (SO_RowId,SO_Quantity,SO_CreationDate)
VALUES
(1,20,'20220101'),
(2,6,'20220303'),
(3,2,'20220303'),
(4,6,'20220401'),
(5,3,'20220501');
DECLARE @Table2 TABLE (
PO_RowId int,
PO_Quantity decimal(15,4),
PO_CreationDate datetime
);
INSERT @Table2 (PO_RowId,PO_Quantity,PO_CreationDate)
VALUES
(1,2,'20220105'),
(2,1,'20220303'),
(3,4,'20220314'),
(4,7,'20220401'),
(5,20,'20220401')
;
WITH t1 AS (
SELECT *, PrevTotal = ISNULL(LAG(RunningTotal) OVER (ORDER BY SO_CreationDate, SO_RowId), 0)
FROM (
SELECT *, RunningTotal = SUM(SO_Quantity) OVER (ORDER BY SO_CreationDate, SO_RowId)
FROM @Table1
) AS t
),
t2 AS (
SELECT *, PrevTotal = ISNULL(LAG(RunningTotal) OVER (ORDER BY PO_CreationDate, PO_RowId), 0)
FROM (
SELECT *, RunningTotal = SUM(PO_Quantity) OVER (ORDER BY PO_CreationDate, PO_RowId)
FROM @Table2
) AS t
)
SELECT t1.SO_RowId,
SO_Quantity = CASE
WHEN t1.RunningTotal > t2.RunningTotal AND t1.PrevTotal >= t2.PrevTotal THEN t2.RunningTotal - t1.PrevTotal
WHEN t1.RunningTotal > t2.RunningTotal AND t1.PrevTotal < t2.PrevTotal THEN t2.PO_Quantity
WHEN t1.RunningTotal <= t2.RunningTotal AND t1.PrevTotal >= t2.PrevTotal THEN t1.SO_Quantity
WHEN t1.RunningTotal <= t2.RunningTotal AND t1.PrevTotal < t2.PrevTotal THEN t1.RunningTotal - t2.PrevTotal
WHEN t2.RunningTotal IS NULL THEN t1.SO_Quantity
END,
t1.SO_CreationDate,
t2.PO_RowId,
PO_Quantity = CASE
WHEN t1.RunningTotal > t2.RunningTotal AND t1.PrevTotal >= t2.PrevTotal THEN t2.RunningTotal - t1.PrevTotal
WHEN t1.RunningTotal > t2.RunningTotal AND t1.PrevTotal < t2.PrevTotal THEN t2.PO_Quantity
WHEN t1.RunningTotal <= t2.RunningTotal AND t1.PrevTotal >= t2.PrevTotal THEN t1.SO_Quantity
WHEN t1.RunningTotal <= t2.RunningTotal AND t1.PrevTotal < t2.PrevTotal THEN t1.RunningTotal - t2.PrevTotal
WHEN t1.RunningTotal IS NULL THEN t2.PO_Quantity
END,
t2.PO_CreationDate
FROM t1
FULL JOIN t2
ON t2.RunningTotal > t1.PrevTotal
AND t2.PrevTotal <= t1.RunningTotal

UNION ALL -- Get leftover amount if total POs > total SOs
SELECT SO_RowId = NULL, 
SO_RowQuantity = NULL, 
SO_CreationDate = NULL, 
t2.PO_RowId, 
PO_Quantity = t2.RunningTotal - t1.RunningTotal, 
t2.PO_CreationDate
FROM (
SELECT TOP 1 * 
FROM t1 
ORDER BY RunningTotal DESC
) AS t1
JOIN t2 
ON t2.RunningTotal > t1.RunningTotal 
AND t2.PrevTotal < t1.RunningTotal

UNION ALL -- Get leftover amount if total SOs > total POs
SELECT t1.SO_RowId,
SO_RowQuantity = t1.RunningTotal - t2.RunningTotal,
t1.SO_CreationDate,
PO_RowId = NULL,
PO_Quantity = NULL,
PO_CreationDate = NULL
FROM (
SELECT TOP 1 * 
FROM t2 
ORDER BY RunningTotal DESC
) AS t2
JOIN t1 
ON t1.RunningTotal > t2.RunningTotal 
AND t1.PrevTotal < t2.RunningTotal
;

编辑了一点，因为我意识到你的要求与我最初认为的可能略有不同。希望这里的逻辑是有意义的。