FIFO存货估价

Question

我使用的2014..My股票交易表是这样的。

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);

我需要在每个月计算2次以下的病例。

1. 剩余量的股票估价

产出：

股票数据估值

Item  Qty      Value
ABC   50    40000.00
XYZ   110   37400.00 

请帮助我在FIFO中找到解决方案。

Answer 1

Select Item, FinalQty as [Final Qty], CurrPrice*FinalQty as [Current Value] from (
 select Item, Sum(Case When TxnType='OUT' Then -Qty Else Qty End) as FinalQty ,
        (Select Top 1 Price from @Stock where Price is not null and s.Item=Item order by Date Desc) as CurrPrice
 from @Stock s
 group by Item ) n