时间戳

首页 > 解决方案 > 获取缺货但即将到来的产品的下一个可用日期

sql - 获取缺货但即将到来的产品的下一个可用日期

问题描述

我正在经营一家从外部 ERP 系统获取所有库存信息的在线商店。我有一些延期交货的产品，我想在产品页面上显示下一个可用日期。

我可以访问 ERP 系统数据库和所有进货交货日期，单个产品的交货情况如下所示：

产品 传入日期 传入
ABC123 20210607 34
ABC123 20210621 17
ABC123 20210705 34
ABC123 20210715 17

我也知道，对于这个特殊的产品，我有 59 件来自客户的延期交货（编号在同一个数据库中可用，我对此有控制权，因此它只是在以下查询中存储为变量 @Backorder（整数））。我目前有 0 件库存。这意味着第一批和第二批货物已经售出，下一个可用日期是 2021-07-05（第三行）。

在过去的几个小时里，我一直在摆弄这个，但我找不到一个好的解决方案。

我最接近的是：

DECLARE @Product AS VARCHAR(100)='ABC123';
DECLARE @Sold AS INT = 59;

SELECT row, product, incoming_date, incoming, available,

CASE WHEN available < 0 THEN 0 ELSE available-LAG(ABS(available),1) OVER (ORDER BY row) END AS new_available

FROM 
(SELECT row, product, incoming_date, incoming,
    CASE WHEN available < 0 THEN available ELSE incoming-ABS(LAG(available) OVER (ORDER BY row)) END AS available

    FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY ArDt ASC) AS row, ProdNo AS product, ArDt AS incoming_date, SUM(NoInvoAb) AS incoming,
        CASE WHEN ROW_NUMBER() OVER(ORDER BY ArDt ASC) = 1 
        THEN SUM(NoInvoAb)-@Sold ELSE 0 END AS available
        FROM OrderLines WHERE TrTp = 6 AND NoInvoAb != 0 AND ProdNo = @Product GROUP BY ArDt, ProdNo
    ) AS A
) AS B

结果是：

排 产品 传入日期 传入 可用的 新的可用
1 ABC123 20210607 34.000000 -25.000000 0.000000
2 ABC123 20210621 17.000000 -8.000000 0.000000
3 ABC123 20210705 34.000000 34.000000 26.000000
4 ABC123 20210715 17.000000 17.000000 -17.000000

正如你所看到的，它有点工作，但是第四行和后面的行它停止给出正确的结果。我将不得不一遍又一遍地循环“new_available”，这是……不可取的。

为调试目的显示完整结果。最后，我不需要表中的所有信息，只需要下一个可用日期，即。2021-07-05 有 26 个单位可用。

这个问题有更好的解决方案吗？关于更好方法的任何想法？

PS：必须在 SQL 查询本身中解决，在网上商店中计算它（目前）不是一个选项。

所有的回应和想法表示赞赏！

标签： sqlsql-servertsql

解决方案

我认为，归根结底，您只需要通过已订购单位的数量校正的累积总和。

您的查询令人困惑。它使用示例中未显示的列。下次请确保发布一致的[示例]。为了简化事情，我将使用下表：

CREATE TABLE elbat
             (product varchar(6),
              incoming_date date,
              incoming integer);

INSERT INTO elbat
            (product,
             incoming_date,
             incoming)
            VALUES
('ABC123', '20210607', 34),
('ABC123', '20210621', 17),
('ABC123', '20210705', 34),
('ABC123', '20210715', 17);

要获取每个交货日期的可用商品数量，您可以使用以下查询，使用sum()窗口函数减去已订购的数量。

DECLARE @ordered integer = 59;
DECLARE @product varchar(6) = 'ABC123';

SELECT product,
       incoming_date date,
       CASE 
         WHEN sum(incoming) OVER (ORDER BY incoming_date) - @ordered < 0 THEN
           0
         ELSE 
           sum(incoming) OVER (ORDER BY incoming_date) - @ordered
       END available
       FROM elbat
       WHERE product = @product;

如果您只对第二天可用性大于零感兴趣，您基本上可以将其放入派生表中并min()在当天使用。

DECLARE @ordered integer = 59;
DECLARE @product varchar(6) = 'ABC123';

SELECT min(date)
       FROM (SELECT incoming_date date,
                    sum(incoming) OVER (ORDER BY incoming_date) - @ordered available
                    FROM elbat
                    WHERE product = @product) x
       WHERE available > 0;

db<>小提琴

推荐阅读