FIFO based stock inventory valuation in SQL Server [closed]

Question

I have a stock transaction table like this:

Item   Date         TxnType Qty  Price
ABC   01-April-2012   IN    200 750.00
ABC   05-April-2012   OUT   100     
ABC   10-April-2012   IN     50 700.00
ABC   16-April-2012   IN     75 800.00
ABC   25-April-2012   OUT   175     
XYZ   02-April-2012   IN    150 350.00
XYZ   08-April-2012   OUT   120     
XYZ   12-April-2012   OUT    10     
XYZ   24-April-2012   IN     90 340.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.00
XYZ   110   37600.00

Please help me to get the solution.

Can you please explain you output what numbers should be summed? — Arion

Damien_The_Unbeliever Damien_The_Unbeliever · Accepted Answer · 2012-04-26T07:02:33

Surprisingly difficult to get right. I suspect it would be easier using SQL Server 2012 which supports running sums in windowing functions. Anyhow:

declare @Stock table (Item char(3) not null,[Date] datetime not null,TxnType varchar(3) not null,Qty int not null,Price decimal(10,2) null)
insert into @Stock(Item ,  [Date] ,        TxnType, Qty,  Price) values
('ABC','20120401','IN',    200, 750.00),
('ABC','20120405','OUT',   100 ,null  ),
('ABC','20120410','IN',     50, 700.00),
('ABC','20120416','IN',     75, 800.00),
('ABC','20120425','OUT',   175, null  ),
('XYZ','20120402','IN',    150, 350.00),
('XYZ','20120408','OUT',   120 ,null  ),
('XYZ','20120412','OUT',    10 ,null  ),
('XYZ','20120424','IN',     90, 340.00);

;WITH OrderedIn as (
    select *,ROW_NUMBER() OVER (PARTITION BY Item ORDER BY [DATE]) as rn
    from @Stock
    where TxnType = 'IN'
), RunningTotals as (
    select Item,Qty,Price,Qty as Total,0 as PrevTotal,rn from OrderedIn where rn = 1
    union all
    select rt.Item,oi.Qty,oi.Price,rt.Total + oi.Qty,rt.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,SUM(Qty) as Qty from @Stock where TxnType='OUT' group by Item
)
select
    rt.Item,SUM(CASE WHEN PrevTotal > out.Qty THEN rt.Qty ELSE rt.Total - out.Qty END * Price)
from
    RunningTotals rt
        inner join
    TotalOut out
        on
            rt.Item = out.Item
where
    rt.Total > out.Qty
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.

The final SELECT statement finds rows which haven't been completely exhausted by outgoing transactions, and then decides whether it's the whole quantity of that incoming transaction, or whether that is the transaction that straddles the outgoing total.

FIFO based stock inventory valuation in SQL Server [closed]

2 Answers