sql - SQL Server 中的 FIFO 查询

Question

我正在用 c# 和 SQL server 构建一个股票管理应用程序。我想从我的表中进行 FIFO 查询。

我以可变价格购买了相同的产品。之后我卖掉了其中一些。我想根据 BatchDate 列以“先进先出”的方式进行查询。所以我想用 PurchasePrice 获取可用的库存产品。

这是我的表格:`

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);

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 here