支持调整的存货账龄报告(FIFO)

Question

在SQL Server中有一个问题需要解决，以便使用基于SKU的FIFO生成库存老化报告；具有调整支持的仓库(入库(+ve(和出库(-ve((。我把方案附在这里了。

仓库代码2021-04-30<100>2021-04-30>>2021-05-10>[/tr>2021-05-112021-05-172021-05-18>[/tr>2021-05-18<1>//tr>

SKU	交易类型	交易日期	数量
100	IN	WH1
100	IN	WH2	50
101	IN	WH1	2021-04-30	30
101	IN	WH2	2021-05-01	25
100	输出	WH2	2021-05-02	30
100	输出	WH1	2021-05-02	20
100	输出	WH1	2021-05-04	50
100	输出	WH2	2021-05-04	20
100	输出	WH1	2021-05-05	25
100	IN	WH2	30
100	IN	WH1	30
101	输出	WH2	2021-05-12	20
100	输出	WH1	2021-05-15	30
102	IN	WH2	2021-05-15	25
102	输出	WH2	2
102	ADJ	WH2	5
102	ADJ	WH2

对于所提供的有限数据，类似的事情可能会做到这一点。

第一个CTE项将"ADJ"行与相应的先前"IN"行联系起来，然后简单地SUM将Qty分组，以导出应用了调整的最终"IN"行将。

现在，我们使用您的原始逻辑(稍作更正(来处理其余部分，不再需要担心调整。

限制：如果存在的调整行完全删除了比先前"；IN"；事务，这将需要更多的逻辑。除非你需要，以下内容应该没问题。

另请注意：您的一些原始"；老化；结果(在问题中(看起来是错误的，并且不是基于相应的"；IN"；一行

更新的小提琴

WITH cte1 AS (
SELECT i.*
, SUM(CASE WHEN TransactionType = 'IN' THEN 1 ELSE 0 END) OVER (PARTITION BY SKU, WarehouseCode ORDER BY TransactionDate) AS grp
FROM inventory AS i
WHERE TransactionType IN ('IN', 'ADJ')
)
, cte2 AS (
SELECT SKU, WarehouseCode, 'IN' AS TransactionType
, MIN(TransactionDate) AS TransactionDate
, SUM(Qty) AS Qty
FROM cte1
GROUP BY WarehouseCode, SKU, grp
UNION
SELECT SKU, WarehouseCode, TransactionType, TransactionDate, Qty
FROM inventory
WHERE TransactionType = 'OUT'
)
, cumulative AS (
SELECT *
, SUM(CASE WHEN TransactionType = 'OUT' THEN Qty ELSE 0 END) OVER (PARTITION BY SKU, WarehouseCode) AS qty_out_final
, SUM(CASE WHEN TransactionType = 'IN'  THEN Qty ELSE 0 END) OVER (PARTITION BY SKU, WarehouseCode ORDER BY TransactionDate) AS qty_in_so_far
FROM cte2
)
SELECT SKU, WarehouseCode, TransactionType, TransactionDate
, qty_out_final, qty_in_so_far, Qty
, CASE WHEN qty_out_final >= qty_in_so_far  THEN 0
ELSE qty_in_so_far - qty_out_final END  AS qty_final
, DATEDIFF(day, TransactionDate, '2021-05-20') + 1             AS aging
FROM cumulative
WHERE TransactionType = 'IN'
ORDER BY TransactionDate, SKU, WarehouseCode
;

结果：

交易类型>qty_out_finalqty_in_so_far>qty_final>100ININ>td>30IN20IN>IN<10>IN>IN>

SKU	仓库代码	交易日期	数量	老化
100	WH1	IN	2021-04-30	125		0	21
100	WH2	2021-04-30		50	50	0	21
101	WH1	2021-04-30	0	30		21
101	WH2	2021-05-01	20	25
100	WH2	2021-05-10	50	80	30	30	11
100	WH1	2021-05-11	125	130
102	WH1	2021-05-15	29	29		27	6
100	WH1	2021-05-16	125	160	30	35