FIFO Stock Valuation Through CTE-Recursion

498 views Asked by At

I have copied it from this site because it's been already closed but I needed it for further solution. thus, kindly help me out.....

Problem : it's calculating the closing stock valuation through FIFO of issue as a whole. but i need cost of issues into Price column in the same row it belongs to itself.

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 result is only Closing Stock as per FIFO Basis as below: Item ClsStock ABC 40000.00 XYZ 37600.00

But I need the followings Result from the same query:

Item Date TxnType OpnQty OpnRate OpnVal InnQty InnRate InnVal OutQty OutRate OutVal ClsQty ClsRate ClsVal

ABC 20120401 IN 200 750 150000 200 750.00 150000

ABC 20120405 OUT 200 750.00 150000 100 750 75000 100 750.00 75000

ABC 20120410 IN 100 750.00 75000 50 700 35000 150 733.33 110000

ABC 20120416 IN 150 733.33 110000 75 800 60000 225 755.56 170000

ABC 20120425 OUT 225 755.56 170000 175 742.86 130000 50 800.00 40000

Total 0 0.00 0 325 753.85 245000 275 745.45 205000 50 800.00 40000

XYZ 20120402 IN 150 350 52500 150 350.00 52500

XYZ 20120408 OUT 150 350.00 52500 120 350 42000 30 350.00 10500

XYZ 20120412 OUT 30 350.00 10500 10 350 3500 20 350.00 7000

XYZ 20120424 IN 20 350.00 7000 90 340 30600 110 341.82 37600

Total 0 0.00 0 240 346.25 83100 130 350 45500 110 341.82 37600

Grand Total 0 #DIV/0! 0 565 580.71 328100 405 618.52 250500 160 485.00 77600

0

There are 0 answers