我有一个像这样的股票交易表：
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
我需要FIFO中每个项目的库存值(先进先出),这意味着应首先消费第一个购买的物品.
上述数据的产出库存估值为：
Item Qty Value ABC 50 40000.00 XYZ 110 37600.00
请帮我解决.
解决方法
令人惊讶的是难以做对.我怀疑使用支持在窗口函数中运行总和的sql Server 2012会更容易.无论如何：
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 ),'20120410',50,700.00),'20120416',75,800.00),'20120425',175,('XYZ','20120402',150,350.00),'20120408',120,'20120412',10,'20120424',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,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
第一个观察是我们不需要对OUT交易做任何特殊的事情 – 我们只需要知道总量.这就是TotalOut CTE计算的结果.前两个CTE使用IN事务,并计算每个代表的库存“间隔” – 将最终查询更改为从RunningTotals中选择*以获得感觉.
最后的SELECT语句查找尚未被传出事务完全耗尽的行,然后确定它是否是该传入事务的全部数量,或者是否是跨越传出总计的事务.
