我手头没有 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(使其有点像交付库存而不是接受库存的订单,并且位于所有其他订单之前).
接下来,计算出订单后产品的总剩余数量是多少;通过对该产品的所有前面的行(按时间顺序)求和。(Giving Stock - Order1 - Order2, etc, etc
)
然后选择新库存水平变为负值的行。