SQL查询项过期日期管理

Question

我有150个订单，商品代码为24。我需要从3中选择id为1,2,75 p的物品。

我如何在SQL Server查询?

数量100100

尝试如下:

With cte AS (
Select id, Itemcode, ExpireDate ,Qty,
Case
When Sum(Qty) OVER (Order By ExpireDate) <=150 
Then Qty
Else
(150-Sum(Qty) OVER (Order By ExpireDate)) + Qty
End AS QtyInOrder
From  MyTable Where itemcode=24 
)
Select 
id, Itemcode, ExpireDate ,Qty, QtyInOrder, (Qty-QtyInOrder) as RemainingInStore
From cte Where QtyInOrder>0 Order By ExpireDate 

Sum(Qty) OVER (Order By ExpireDate)将返回到期日的累计数量。

在给定的商品行中，如果累计金额小于总订单数量(在您的示例中为150)，则所有这些商品数量将包含在订单中。否则包括的数量将是(150-Sum(Qty) OVER (Order By ExpireDate)) + Qty，即对于item3，它将是150 - 175 + 100 = 75。为了保持这个值，我在cte中添加了额外的字段，称为QtyInOrder，当然您可以从选择查询中删除它。

对于未包含在总订单中的项目，QtyInOrder的值将小于或等于零，因此我在从cte中选择时使用Where QtyInOrder>0。

查看dbfiddle的结果。

您可以在这里使用滚动求和方法:

WITH cte AS (
SELECT *, SUM(Qty) OVER (ORDER BY ExpireDate) AS RollingQty
FROM yourTable
),
cte2 AS (
SELECT TOP 1 ExpireDate
FROM cte
WHERE 150 - RollingQty <= 0
ORDER BY ExpireDate
)
SELECT *
FROM yourTable
WHERE ExpireDate <= (SELECT ExpireDate FROM cte2);

上面的逻辑首先计算数量的滚动总和，按到期日排序。然后，第二个CTE找到满足150个数量要求的第一个到期日期值。然后我们选择所有有效期小于或等于此日期的记录。

这是一个工作演示。