tsql – SQL Server中基于FIFO的库存库存评估

我有一个像这样的股票交易表：

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

请帮我解决.

解决方法

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语句查找尚未被传出事务完全耗尽的行,然后确定它是否是该传入事务的全部数量,或者是否是跨越传出总计的事务.

（编辑：东莞站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!