我有下面的库存交易记录表，我想用它来确定我销售的单元的使用年限。
我想要一个脚本，允许我输入要购买的单元数量，结果显示将根据FIFO逻辑选择哪些单元。
类型标识事务是IN还是OUT。
示例：一位客户希望在1月31日购买50套。
我的桌子在1月31日之前有以下交易。
id date units type
1 Jan 1 10 1
2 Jan 10 45 1
3 Jan 12 -15 -1
4 Jan 25 20 1
步骤1:使用SQL窗口函数应用FIFO
drop table if exists ##transactions
go
select *
into ##transactions
FROM (
select 1 id, '1/1/2020' date, 10 units, 1 type UNION ALL
select 2 id, '1/10/2020' date, 45 units, 1 type UNION ALL
select 3 id, '1/12/2020' date, -15 units, -1 type UNION ALL
select 4 id, '1/25/2020' date, 20 units, 1 type
) a
SELECT id, date, units, IIF(cumulative > 0, cumulative, 0) cumulative FROM (
SELECT id, date, units, SUM(units) OVER (ORDER BY type, date) AS cumulative FROM ##transactions
) a
ORDER BY date
结果：
id date units cumulative
1 Jan 1 10 0
2 Jan 10 45 40
3 Jan 12 -15 0
4 Jan 25 20 60
步骤2:选取X个单位(其中X=50(
code help please
结果(所需(：
id date units cumulative selected
1 Jan 1 10 0 0
2 Jan 10 45 40 40
3 Jan 12 -15 0 0
4 Jan 25 20 60 10
这将告诉我，我将从1月10日的库存中挑选40台，从1月25日的库存中将挑选10台。
我希望该过程以一种我可以输入任何数字的方式工作，结果集将返回基于FIFO的正确可用值。
declare @amount_to_deduct int = 50
SELECT *,
case when cumulative = 0 or units < 0 then 0
when cumulative < @amount_to_deduct and cumulative < units then cumulative
when cumulative < @amount_to_deduct and cumulative >= units then units
when cumulative - units < @amount_to_deduct and cumulative < units then @amount_to_deduct
when cumulative - units < @amount_to_deduct and cumulative >= units then @amount_to_deduct - cumulative + units
else 0
end as selected
FROM (
SELECT id, date, units, IIF(cumulative > 0, cumulative, 0) cumulative
FROM (
SELECT id, date, units, SUM(units) OVER (ORDER BY type, date) AS cumulative FROM transactions
) a
) b
ORDER BY date
这里有一个sql篡改了一些额外的测试数据：http://sqlfiddle.com/#！18/b0584/10
我使用该样本对50、30、100和150个病例进行了测试，数据看起来是正确的
我认为这是cumulative和条件逻辑的窗口和：
select id, date, units,
case
when sum(cumulative) over(order by date) < 50
then cumulative
when sum(cumulative) over(order by date) - cumulative < 50
then 50 - sum(cumulative) over(order by date) + cumulative
else 0
end as selected
from (
select id, date, units,
case when sum(units) over (order by type, date) > 0
then sum(units) over (order by type, date)
else 0
end as cumulative
from ##transactions
) a
order by date
DB Fiddle上的演示-我在表的末尾添加了另一行，以演示"；下一个"；达到目标数量后的库存行：
id|date|units|selected-：|：--------|----：|-------：2020年1月1日| 10 |02020年10月2日| 45 | 402020年12月3日|-15 |02020年4月25日| 10年1月20日2020年4月1日至26日| 0年30日