SQL Server 中的 FIFO 查询

我正在构建一个库存管理应用程序c# with 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);

使用创建的“运行总和”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           
  

数据库小提琴