我没有sql Server,并且sqlfiddle最近与我的配合不好,因此未经测试,但是逻辑应该可以工作…
WITH
stock_changes
AS
(
SELECT supplier, Destination, Req_Time, Prd_Code, Prd_Description, -Qty AS Qty FROM orders
UNION ALL
SELECT supplier, NULL, '00:00', Prd_Code, Prd_Desc, Stock FROM stock
),
stock_post_order
AS
(
SELECT
*,
SUM(Qty) OVER (PARTITION BY supplier, Prd_Code
ORDER BY Req_Time
ROWS UNBOUNDED PRECEDING
)
AS new_qty
FROM
stock_changes
)
SELECT
*,
CASE WHEN new_qty > qty THEN new_qty ELSE qty END AS order_shortfall
FROM
stock_post_order
WHERE
new_qty < 0
首先将您的订单数量反转为负,因此它们就是库存水平将要改变的数量。
接下来,将您的库存水平与订单结合起来,所需时间为0 (使其有点像交付库存而不是库存的订单,并且先于所有其他订单) 。
接下来,计算出订购后产品的总剩余数量;通过汇总该产品的所有先前行(按时间顺序)。 (给予Stock - Order1 - Order2, etc, etc
)
然后选择新库存水平为负的行。