SQL Server 中基于 FIFO 的库存评估 [英] FIFO based stock inventory valuation in SQL Server
问题描述
我有一个这样的股票交易表:
I have a stock transaction table like this:
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(先进先出)中每件商品的库存值,这意味着应该先消费第一个购买的商品.上述数据的产出存货估值为:
I need the value of the inventory for each item in FIFO (First in first out) meaning the first purchased item should be consumed first. The output stock valuation of the above data is:
Item Qty Value
ABC 50 40000.00
XYZ 110 37600.00
请帮我找到解决方案.
推荐答案
出人意料地难以正确处理.我怀疑使用支持在窗口函数中运行总和的 SQL Server 2012 会更容易.无论如何:
Surprisingly difficult to get right. I suspect it would be easier using SQL Server 2012 which supports running sums in windowing functions. Anyhow:
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
第一个观察结果是我们不需要为 OUT
交易做任何特别的事情——我们只需要知道总数量.这就是 TotalOut
CTE 计算出来的.前两个 CTE 与 IN
交易一起使用,并计算每个代表的股票间隔" - 将最终查询更改为仅 select * from RunningTotals
以感受一下.
The first observation is that we don't need to do anything special for OUT
transactions - we just need to know the total quantity. That's what the TotalOut
CTE calculates. The first two CTEs work with IN
transactions, and compute what "interval" of stock each represents - change the final query to just select * from RunningTotals
to get a feel for that.
最后的 SELECT
语句查找尚未被传出事务完全耗尽的行,然后决定它是传入事务的全部数量,还是跨越传出事务的事务总计.
The final SELECT
statement finds rows which haven't been completely exhausted by outgoing transactions, and then decides whether it's the whole quantity of that incoming transaction, or whether that is the transaction that straddles the outgoing total.
这篇关于SQL Server 中基于 FIFO 的库存评估的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!