MSSQL ERP 数量均摊
MSSQL ERP 数量均摊
需求背景
以自己的学习结果做了一个调整，防止有小伙伴看不懂CTE
以销售订单为例
| 行号 | 物料编码 | 数量 |
|---|---|---|
| 1 | A001 | 100 |
| 2 | A001 | 200 |
| 3 | A001 | 30 |
| 4 | B001 | 200 |
| 5 | B001 | 280 |
库存数据
| 行号 | 物料编码 | 库存数量 |
|---|---|---|
| 1 | A001 | 280 |
| 2 | B001 | 500 |
希望看到的结果
| 行号 | 物料编码 | 数量 | 库存数量 |
|---|---|---|---|
| 1 | A001 | 100 | 100 |
| 2 | A001 | 200 | 180 |
| 3 | A001 | 30 | 0 |
| 4 | B001 | 200 | 200 |
| 5 | B002 | 280 | 300 |
DECLARE @SalOrder TABLE
(
FMaterialId NVARCHAR(255),
FQty DECIMAL(18,10)
)
insert into @SalOrder values
('A001',100),
('A001',200),
('A001',30),
('B001',200),
('B001',280)
DECLARE @StockQty TABLE
(
FMaterialId NVARCHAR(255),
FQty DECIMAL(18,10)
)
insert into @StockQty values
('A001',280),
('B001',500)
-- 先把数据拼接在一起
SELECT
ROW_NUMBER() OVER(ORDER BY T.FMaterialId) AS FID
,ROW_NUMBER() OVER(PARTITION BY T.FMaterialId ORDER BY T.FMaterialId) AS FRow
,T.FMaterialId '物料ID',T.FQty '订单数量',T1.FQty AS '库存数'
,CAST(0 AS DECIMAL(18,10)) AS '预留需求汇总数'
,CAST(0 AS DECIMAL(18,10)) AS '预留计算结存数'
,CAST(0 AS DECIMAL(18,10)) AS '预留分摊结果'
INTO #Temp
FROM @SalOrder T
JOIN @StockQty T1 ON T.FMaterialId = T1.FMaterialId
-- 开始处理汇总数
UPDATE T
SET T.预留需求汇总数 = (SELECT SUM(X.订单数量) FROM #Temp X WHERE X.物料ID = T.物料ID AND X.FID <= T.FID),
T.预留计算结存数 = T.库存数 - (SELECT SUM(X.订单数量) FROM #Temp X WHERE X.物料ID = T.物料ID AND X.FID <= T.FID)
FROM #Temp T
-- 初步处理分摊结果
UPDATE T
SET T.预留分摊结果 =
case when T.预留计算结存数 >= 0 then T.订单数量
when T.预留计算结存数 <0 and ABS(T.预留计算结存数) <= T.订单数量 then T.订单数量 + T.预留计算结存数
when T.预留计算结存数 < 0 and ABS(T.预留计算结存数) > T.订单数量 then 0 END
FROM #Temp T
-- 最终处理，预留剩余的需要加上去，也就是当尾行大于0的时候需要处理
UPDATE T
SET T.预留分摊结果 = T.预留分摊结果 + T.预留计算结存数
FROM #Temp T
WHERE T.预留计算结存数 >= 0 AND FID = (SELECT MAX(FID) FROM #Temp X WHERE T.物料ID = X.物料ID)
SELECT * FROM #Temp
DROP TABLE #Temp
| FID | FRow | 物料ID | 订单数量 | 库存数 | 预留需求汇总数 | 预留计算结存数 | 预留分摊结果 |
|---|---|---|---|---|---|---|---|
| 1 | 1 | A001 | 100.0000000000 | 280.0000000000 | 100.0000000000 | 180.0000000000 | 100.0000000000 |
| 2 | 2 | A001 | 200.0000000000 | 280.0000000000 | 300.0000000000 | -20.0000000000 | 180.0000000000 |
| 3 | 3 | A001 | 30.0000000000 | 280.0000000000 | 330.0000000000 | -50.0000000000 | 0.0000000000 |
| 4 | 1 | B001 | 200.0000000000 | 500.0000000000 | 200.0000000000 | 300.0000000000 | 200.0000000000 |
| 5 | 2 | B001 | 280.0000000000 | 500.0000000000 | 480.0000000000 | 20.0000000000 | 300.0000000000 |
最终的分摊结果与我们希望一致。
PS:测试的时候可以把Update一行行注释来看效果。
20240705换个简单的脚本方案实现。
DECLARE @SalOrder TABLE
(
FMaterialId NVARCHAR(255),
FQty DECIMAL(18,10)
)
insert into @SalOrder values
('A001',100),
('A001',200),
('A001',30),
('B001',200),
('B001',280)
DECLARE @StockQty TABLE
(
FMaterialId NVARCHAR(255),
FQty DECIMAL(18,10)
)
insert into @StockQty values
('A001',280),
('B001',500);
WITH orderDetail AS
(
SELECT
T.*,SUM(T.FQty) OVER(PARTITION BY T.FMaterialId ORDER BY T.FQty) AS FTotalQty,T1.FQty AS 'FStockQty'
FROM @SalOrder T
LEFT JOIN @StockQty T1 ON T.FMaterialId = T1.FMaterialId
)
SELECT *
,CASE WHEN T.FStockQty >= T.FTotalQty THEN 0
WHEN T.FStockQty < T.FTotalQty AND ABS(T.FStockQty - T.FTotalQty) >= T.FQty THEN T.FQty
WHEN T.FStockQty < T.FTotalQty AND ABS(T.FStockQty - T.FTotalQty) < T.FQty THEN ABS(T.FStockQty - T.FTotalQty)
ELSE 0 END AS 'FLackQty'
FROM orderDetail T
