FIFO SQL查询库存和价值

小编典典

FIFO SQL查询库存和价值

sql

我有一个这样的库存分类帐表：

    item  date           time      in_qty  out_qty  in_Price  out_Price
    ABC   01-April-2012  00:00:00  200              750.00   
    ABC   05-April-2012  00:00:00          100                800.00     
    ABC   10-April-2012  00:00:00  50               700.00                      
    ABC   16-April-2012  00:00:00  75               800.00
    ABC   25-April-2012  00:00:00          175                850.00
    XYZ   02-April-2012  00:00:00  150              350.00
    XYZ   08-April-2012  00:00:00          120               380.00
    XYZ   12-April-2012  00:00:00          80                370.00
    XYZ   24-April-2012  00:00:00  80               330.00
    XYZ   24-April-2012  00:00:00  90               340.00      
    LPQ   26-April-2012  00:00:00  70               240.00
    LPQ   27-April-2012  00:00:00  30               230.00

我需要FIFO（先进先出）中的每个物料的库存值，这意味着第一个购买的物料应首先消耗。以上数据的输出库存评估为：

    Item    Qty   Value
    ABC     50    40000
    XYZ     110   40500
    LPQ     100   23700

请帮助我在FIFO中获得解决方案

阅读 180

2021-04-22

共1个答案

小编典典

我达到了我的期望，

declare @Stock table (item char(3) not null,[date] date not null,[time] time not null,in_qty decimal(18,5) null,out_qty decimal(18,5) null,in_Price decimal(10,2) null,out_Price decimal(10,2) null)
insert into @Stock(item , [date] ,[time] , in_qty, out_qty, in_Price,out_Price) values
('ABC','20120401','00:00:00',200 ,0 ,750 ,0),
('ABC','20120401','00:00:00',0 ,100 ,0 ,800),
('ABC','20120401','00:00:00',50 ,0 ,700 ,0),
('ABC','20120401','00:00:00',75 ,0 ,800 ,0),
('ABC','20120401','00:00:00',0 ,175 ,0 ,850),
('XYZ','20120401','00:00:00',150 ,0 ,350 ,0),
('XYZ','20120401','00:00:00',0 ,120 ,0 ,380),
('XYZ','20120401','00:00:00',0 ,80 ,0 ,370),
('XYZ','20120401','00:00:00',80 ,0 ,330 ,0),
('XYZ','20120401','00:00:00',90 ,0 ,340 ,0),
('PQR','20120401','00:00:00',70 ,0 ,240 ,0),
('PQR','20120401','00:00:00',30 ,0 ,230 ,0)

;WITH OrderedIn as 
    (
        select *,ROW_NUMBER()OVER (PARTITION BY item ORDER BY date asc,time asc) as S_No
        from @Stock
        where in_qty <> 0
    ),  RunningTotals as 
    (
        select item, in_qty, in_Price as price, cast(in_qty as varchar(100)) as Total
                ,cast(0 as varchar(100)) as PrevTotal,S_No from OrderedIn where S_No = 1
        union all
        select rt.item ,oi.in_qty ,oi.in_Price as price 
                ,cast(rt.Total + oi.in_qty as varchar(100)),cast(rt.Total as varchar(100)),oi.S_No
        from
                RunningTotals rt
            inner join OrderedIn oi 
                on rt.item = oi.item
                and rt.S_No = oi.S_No - 1
    ), TotalOut as 
    (
        select item,SUM(out_qty) as Qty from @Stock where out_Price <> 0 group by item
    )
    select  
         rt.item
        ,SUM(CASE WHEN PrevTotal > COALESCE(out.Qty,0) THEN rt.in_qty ELSE rt.Total - COALESCE(out.Qty,0) END) as Qty
        ,SUM(CASE WHEN PrevTotal > COALESCE(out.Qty,0) THEN rt.in_qty ELSE rt.Total - COALESCE(out.Qty,0) END * (price)) as Value
    from
        RunningTotals rt
            left join
        TotalOut out
            on
                rt.item = out.item
    where
        rt.Total > COALESCE(out.Qty,0)
    group by rt.item

第一个观察结果是，我们不需要对OUT交易做任何特殊的事情-我们只需要知道总数量即可。这就是TotalOut
CTE的计算结果。前两个CTE处理IN事务，并计算每个代表的库存“时间间隔”-更改最终查询以仅从RunningTotals中选择*，以了解这一点。

如果您有单独的日期和时间列，那么您应该先按日期分组，然后再按时间分组。

先进先出（先进先出）评估按日期和时间按升序分组

2021-04-22