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