FIFO规则下跨仓ADJQTY与IN库存计算的SQL实现难题

现有一组库存交易数据，需按FIFO（先进先出）逻辑计算库存，但需遵循以下特殊规则：

• IN类型交易：需按**工厂(plant)、仓库(warehouse)、零件(part)、货位(bin)**维度统计库存
• ADJQTY类型交易：仅按**零件(part)**维度，严格按交易顺序执行FIFO扣减，完全忽略其记录的工厂、仓库信息

尝试过分开计算两类交易后合并、多种CTE方案（排序、逆序计算、LEAD/LAG分区函数），但因两类交易计算维度差异，始终无法得到正确结果。

测试数据SQL #

create table #test
(plant nvarchar(10), warehouse nvarchar(50),part nvarchar(10), bin int, trantype nvarchar (10), trannum int, trandate date, qty int)

insert into #test values
('plant1','warehouse_4','part1',1,'IN',1,'2016-05-20',3),
('plant1','warehouse_4','part1',1,'IN',2,'2016-11-02',2),
('plant2','warehouse_3','part1',1,'IN',3,'2017-03-10',5),

('plant2','warehouse_3','part1',1,'ADJQTY',4,'2017-03-30',-1),  
---程序记录为plant2、warehouse_3，但需按FIFO从第一行（warehouse_4、plant1）扣减
('plant2','warehouse_3','part1',1,'ADJQTY',5,'2017-04-27',-1),  
---程序记录为plant2、warehouse_3，但需按FIFO从第一行（warehouse_4、plant1）扣减
('plant2','warehouse_3','part1',1,'ADJQTY',6,'2017-07-11',-2),
--此时应得到：plant1 warehouse_4库存1，plant2 warehouse_3库存5

('plant2','warehouse_3','part1',1,'IN',7,'2017-08-18',4),
---此时应得到：plant1 warehouse_4库存1，plant2 warehouse_3库存9

('plant2','warehouse_3','part1',1,'ADJQTY',8,'2017-08-31',-1),
---需忽略其工厂仓库，扣减warehouse_4库存至0，仅剩warehouse_3库存9

('plant1','warehouse_4','part1',NULL,'ADJQTY',9,'2017-09-29',-5),
---程序记录为plant1、warehouse_4且无货位，但需按FIFO从plant2 warehouse_3（trannum3）扣减，使其库存变为4

('plant2','warehouse_3','part1',1,'ADJQTY',10,'2017-10-13',-2),
---此时应得到：warehouse_3库存2

('plant1','warehouse_4','part1',1,'IN',11,'2018-10-10',4)
---最终应得到：
--plant2 warehouse_3库存2
--plant1 warehouse_4库存4

select * from #test

drop table #test

计算逻辑与预期结果 #

1. 初始入库后：
   • plant1 warehouse_4 part1 bin1：3+2=5
   • plant2 warehouse_3 part1 bin1：5
2. 三次ADJQTY累计扣减4：按FIFO优先扣减plant1 warehouse_4库存，剩余1；plant2 warehouse_3库存保持5
3. 再次入库后：plant2 warehouse_3库存增加4，变为9；plant1 warehouse_4仍为1
4. ADJQTY扣减1：扣减plant1 warehouse_4剩余库存至0；plant2 warehouse_3保持9
5. ADJQTY扣减5：按FIFO从plant2 warehouse_3库存中扣减，剩余4
6. ADJQTY扣减2：plant2 warehouse_3库存剩余2
7. 最后入库后：plant1 warehouse_4库存增加至4

最终库存结果：

• plant2 warehouse_3 part1：2
• plant1 warehouse_4 part1：4

内容的提问来源于stack exchange，提问作者siyuri0907