SQL Server中的FIFO查询 [英] a FIFO query in SQL Server

问题描述

我正在使用SQL server在c#中构建库存管理应用程序.我想从我的表中进行FIFO查询.

I'm building a stock management app in c# with SQL server. I want to make a FIFO query from my table.

我以可变价格购买了相同的产品.之后，我卖了一些.我想根据BatchDate列基于先进先出"进行查询.因此，我想以PurchasePrice获得库存产品中的可用产品.

I purchased same products in variable rate. After that I sold some of them. I want to query based in "First in first out" according to BatchDate column. So I want to get the available in stock products with 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逻辑确定库存何时超过库存.

using "running sums" created by sum(...) over(...) for stockin and stockout allows us to determine when stockin exceeds stockout using FIFO logic.

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 此处

这篇关于SQL Server中的FIFO查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！