FIFO SQL Query for stock and value

Question

I have a stock Ledger table like this:

    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  

I need the value of the inventory for each item in FIFO (First in first out) meaning the first purchased item should be consumed first. The output stock valuation of the above data is:

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

Please help me to get the solution in FIFO

Answer 1

I got what I expected ,

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

The first observation is that we don't need to do anything special for OUT transactions - we just need to know the total quantity. That's what the TotalOut CTE calculates. The first two CTEs work with IN transactions, and compute what "interval" of stock each represents - change the final query to just select * from RunningTotals to get a feel for that.

If you have separate columns for date and time ,Then you should group by date first then time.

• In FIFO (First in first out) valuation Group by for date and time like ascending