запрос FIFO в SQL Server

Я создаю приложение для управления запасами в 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);

введите здесь описание изображения


sql sql-server fifo
person Lutfor Rahman    schedule 04.11.2017    source источник
comment
rusanu.com/2010/03/26/using-tables-as -очереди   -  person Martin Smith    schedule 04.11.2017
comment
Во-первых, не публикуйте структуру таблиц, образцы данных и код в виде изображений, они нам нужны в виде форматированного текста, во-вторых, что вы пробовали до сих пор? в-третьих, будьте ясны и укажите свою проблему.   -  person Ilyes    schedule 04.11.2017
comment
Мне нужно, сколько товара и стоимость   -  person Lutfor Rahman    schedule 04.11.2017
comment
Вам нужно сначала попробовать сделать это самостоятельно. SO - это не сервис написания кода. Скорее всего, вам нужна sum () по вашим датам, с суммами входа и выхода.   -  person James Z    schedule 05.11.2017

Ответы (2)


arrow_upward
2
arrow_downward

использование «текущих сумм», созданных 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 здесь

person Paul Maxwell    schedule 06.11.2017

arrow_upward
1
arrow_downward

Сначала я создал ПРОЦЕДУРУ. Позже данные ПРОЦЕДУРЫ объединяют другую сторону процедуры с другой ТАБЛИЦЕЙ.

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
person Lutfor Rahman    schedule 06.11.2017