SQL Server中的FIFO查询

Question

我正在使用C#和SQL Server构建一个库存管理应用程序。 我想从我的表中进行FIFO查询。

我购买了相同的产品，但价格不同。之后我卖掉了其中一部分。我想根据BatchDate列按“先进先出”的原则进行查询。因此，我想获取当前可用的库存产品及其购买价格。

这是我的表格:

CREATE TABLE InventoryLedgers
(

    BatchNo nvarchar(30),
    BatchDate datetime,
    ProductId int,
    StockIn decimal(18, 2),
    StockOut decimal(18, 2),
    PurchasePrice decimal(18, 2),
    SalesPrice decimal(18, 2)
);


INSERT INTO InventoryLedgers (BatchNo,BatchDate ,ProductId ,StockIn ,StockOut ,PurchasePrice ,SalesPrice)
VALUES ('JRV171000001', '10/20/2017', 1, 2, 0, 35000, 0);

INSERT INTO InventoryLedgers (BatchNo,BatchDate ,ProductId ,StockIn ,StockOut ,PurchasePrice ,SalesPrice)
VALUES ('JRV171000002', '10/21/2017', 1, 3, 0, 36000, 0);

INSERT INTO InventoryLedgers (BatchNo,BatchDate ,ProductId ,StockIn ,StockOut ,PurchasePrice ,SalesPrice)
VALUES ('JRV171000003', '10/22/2017', 1, 5, 0, 37000, 0);

INSERT INTO InventoryLedgers (BatchNo,BatchDate ,ProductId ,StockIn ,StockOut ,PurchasePrice ,SalesPrice)
VALUES ('JRV171000004', '10/20/2017', 2, 3, 0, 40000, 0);

INSERT INTO InventoryLedgers (BatchNo,BatchDate ,ProductId ,StockIn ,StockOut ,PurchasePrice ,SalesPrice)
VALUES ('JRV171000005', '10/21/2017', 2, 3, 0, 42000, 0);

INSERT INTO InventoryLedgers (BatchNo,BatchDate ,ProductId ,StockIn ,StockOut ,PurchasePrice ,SalesPrice)
VALUES ('JRV171000006', '10/22/2017', 2, 5, 0, 46000, 0);


INSERT INTO InventoryLedgers (BatchNo,BatchDate ,ProductId ,StockIn ,StockOut ,PurchasePrice ,SalesPrice)
VALUES ('JRV171000007', '10/22/2017', 1, 0, 3, 0, 45000);

INSERT INTO InventoryLedgers (BatchNo,BatchDate ,ProductId ,StockIn ,StockOut ,PurchasePrice ,SalesPrice)
VALUES ('JRV171000008', '10/22/2017', 2, 0, 4, 0, 50000);

- Lutfor Rahman

---

http://rusanu.com/2010/03/26/using-tables-as-queues/ - Martin Smith

首先，请勿将表结构、示例数据和代码作为图像发布，我们需要它们作为格式化文本；其次，请说明您已经尝试了什么；第三，请明确并指定您的问题。 - Ilyes

我需要知道有多少产品和它们的价值。 - Lutfor Rahman

你需要尝试自己去做。SO不是一个代码编写服务。很可能你需要对日期进行sum()，并计算进出金额。 - James Z

Answer 1

通过使用sum(...) over(...)创建的“运行总和”来处理进货和出货，我们可以使用FIFO逻辑确定何时进货超过出货。

WITH cte AS (
      SELECT
            *
      , sum(stockin)  over(partition by ProductId order by BatchDate ASC) sum_in
      , sum(stockout) over(partition by ProductId order by BatchDate ASC) sum_out
      FROM InventoryLedgers
      )
SELECT
      i.id, i.BatchNo, i.BatchDate ,i.ProductId ,i.StockIn
    , i.PurchasePrice, i.sum_in - o.sum_out as tot_avail_stock
FROM cte i
inner join (
            select *
            from cte
            where stockout > 0
          ) o on i.ProductId = o.ProductId and i.sum_in > o.sum_out
where i.stockin > 0
order by productid, batchdate
GO

id | BatchNo      | BatchDate  | ProductId | StockIn | PurchasePrice | tot_avail_stock
-: | :----------- | :--------- | --------: | :------ | :------------ | :--------------
 2 | JRV171000002 | 21/10/2017 |         1 | 3.00    | 36000.00      | 2.00           
 3 | JRV171000003 | 22/10/2017 |         1 | 5.00    | 37000.00      | 7.00           
 5 | JRV171000005 | 21/10/2017 |         2 | 3.00    | 42000.00      | 2.00           
 6 | JRV171000006 | 22/10/2017 |         2 | 5.00    | 46000.00      | 7.00           

在这里查看dbfiddle的链接

- Paul Maxwell

Answer 2

起初我创建了一个存储过程。后来，该存储过程的数据与另一个表连接在了一起。

WITH OrderedIn as (
    select ProductId Item, Date = BatchDate , Qty=StockIn, Price = PurchasePrice, ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY ProductId, [BatchDate]) as rn
    from InventoryLedgers
    where StockIn > 0
),
RunningTotals as (
    select Item, Qty, Price, CAST(Qty AS int) AS  Total, CAST(0 AS int) as PrevTotal, rn from OrderedIn where rn = 1
    union all
    select rt.Item, oi.Qty, oi.Price, CAST(rt.Total AS int)  + CAST(oi.Qty AS int), CAST(rt.Total AS int) Total, oi.rn
    from
        RunningTotals rt
            inner join
        OrderedIn oi
            on
        rt.Item = oi.Item and rt.rn = oi.rn - 1
), TotalOut as (
    select Item=ProductId, SUM(StockOut) as Qty from InventoryLedgers where StockOut>=0 group by ProductId
)
select
    rt.Item, SUM(CASE WHEN CAST(PrevTotal AS int) > out.Qty THEN rt.Qty ELSE CAST(rt.Total AS int) - out.Qty END * Price) price
from
    RunningTotals rt
        inner join
    TotalOut out
        on
            rt.Item = out.Item
where
    CAST(rt.Total AS int) > CAST(out.Qty AS int)
group by rt.Item

- Lutfor Rahman

Answer 3

Paul的答案累积了tot_available_stock，这可能对库存跟踪来说不直观。此外，他的答案没有涵盖从未销售的库存情况。

在Paul的答案基础上，这是我的修订解决方案。

以下是我的测试数据集。我添加了适合我的情况的仓库。

id| ProductID | StockIn | StockOut | Price |  BatchDate | WH |
-----------------------------------------------------------------
01|         A |       5 |        0 |    20 | 2020-01-01 | AZ |
02|         A |      10 |        0 |    30 | 2020-02-01 | AZ |
03|         A |       0 |       12 |    20 | 2020-02-02 | AZ |
04|         A |       1 |        0 |    30 | 2020-02-03 | AZ |
05|         B |       1 |        0 |   100 | 2020-02-03 | AZ |
06|         A |       1 |        0 |    50 | 2020-02-04 | CA |

以下是我的解决方案（MySQL）

WITH cte AS (
      SELECT
            *
      , sum(stockin)  over(partition by ProductId, WH order by BatchDate ASC) sum_in
      , sum(stockout) over(partition by ProductId, WH order by BatchDate ASC) sum_out
      FROM InventoryLedgers
      )
SELECT
      i.id, i.ProductId, i.sum_in - o.sum_out as tot_avail_stock, i.Price, i.BatchDate, i.WH
FROM cte i
left join (
            select ProductID, WH, max(sum_out) as sum_out
            from cte
            where stockout > 0
          ) o on i.ProductId = o.ProductId and i.WH = o.WH and i.sum_in > o.sum_out
WHERE 
      i.stockin > 0
HAVING 
      tot_available_stock is not null
UNION ALL
Select 
      id, ProductID, StockIn as tot_avail_stock, BatchDate, WH
FROM 
      InventoryLedgers
where
      (Target, WH) NOT IN(
      Select 
            ProdctID, WH
      FROM
            InventoryLedgers
      WHERE 
            Sell_Qty>0)
ORDER BY 
      WH, ProductID;

结果看起来像这样

id| ProductID | tot_avail_stock | Price |  BatchDate | WH |
-----------------------------------------------------------------
02|         A |               3 |    30 | 2020-02-01 | AZ |
04|         A |               1 |    30 | 2020-02-03 | AZ |
05|         B |               1 |   100 | 2020-02-03 | AZ |
06|         A |               1 |    50 | 2020-02-04 | CA |

- Lee Tom