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语句查找尚未被传失事宜完全耗尽的行,然后确定它是否是该传入事宜的所稀有目,可能是否是超过传出总计的事宜.

（编辑：湖南网）

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