我有一张表,其中列出了我们当前的年度需求预测与上一年度需求预测。这给了我们今年每周累计需要多少零件。这也给了我们之前说过的一年中每周累计需要多少零件。最后,它告诉我们供应商在预测之间实际向我们交付了多少零件。
我取以前的预测数量减去收到的数量,并将其与我们新的预测数量进行比较。这告诉我们预测之间的需求是否增加。
问题是,如果需求发生了积极变化(这意味着他们交付的数量超过了我们所说的需求(,那么我需要将该数量应用于下一行。这需要继续,直到该列再次变为负数。换句话说,如果该行与前一行之间的差最终为正,则将该行设置为零,并将剩余余额应用于下一行的相同公式。
我附上了示例数据,以显示我拥有什么和我想要什么。此外,这是针对许多不同零件号的许多预测。我想不出一种方法来让一份案件陈述对这件事起作用。我也想过使用窗口函数,但每次该列开始获得负值时,我都想不出停止(或将结转的余额重置为0(的方法。
DataAP | 上次预测的需求 | 交付数量新的预测需求 | 需求变化 | ><1th>列I需要<2th>剩余结转 | |||||
---|---|---|---|---|---|---|---|---|---|
1 | 2720 | >980 | 2170 | 430 | 0 | 430 | |||
2 | 3440 | 427 | 2845 | -1680 | 262 | ||||
3 | 4160 | >696 | 352056 | 0 | 318 | ||||
4 | 4880 | 空 | 3970 | -910td>-592>0 | |||||
5 | 5836 | 空 | 4956-880 | ||||||
6 | 6156 | 空 | 5356 | >-800 | -800|||||
7 | 7036 | 空 | 5756 | -1280 | -1280|||||
8 | 7756 | 空 | 6876 | -880>||||||
9 | 8156 | 空 | 6876 | -1280 | -12800|||||
10 | 8636 | 空 | >7836 | -800 | -800
假设一个表有两列,pk和d,其中列"pk"是唯一的序数整数,列"d"包含需求更改。在下面的查询中,"case_logic"列中的case语句将出现,以生成您要查找的输出。
declare @T table (
pk int unique not null,
d int);
insert into @T(pk, d) values
(1, 430),
(2, -168),
(3, 56),
(4, -910),
(5, -880),
(6, -800),
(7, -910),
(8, -880),
(9, -800),
(10, -910),
(11, -880),
(12, -800);
select *,
sum(d) over (order by pk) sum_over,
case when (sum(d) over (order by pk))>0 then 0
when abs(d)>abs(sum(d) over (order by pk)) then sum(d) over (order by pk)
else d end case_logic
from @t;
pk d sum_over case_logic
1 430 430 0
2 -168 262 0
3 56 318 0
4 -910 -592 -592
5 -880 -1472 -880
6 -800 -2272 -800
7 -910 -3182 -910
8 -880 -4062 -880
9 -800 -4862 -800
10 -910 -5772 -910
11 -880 -6652 -880
12 -800 -7452 -800
我想不出任何简洁的基于集合的方法来实现这一点,因为这实际上是一种递归关系-您想要计算的结转是基于对前几行进行的相同计算。因此,我认为您需要在行上循环,要么使用光标,要么使用类似以下的递归CTE:
-- ASSUMING THAT DataAP is a primary key - replace with the actual key if needed
-- Patitioning by Forecast_ID, Part_number - change to any columns you need
-- BUT make sure that the join clause in the CTE matches the partition by columns
-- used to generate row_number
drop table if exists #IntermediateResults
Select *
, [row_number] = ROW_NUMBER() over (partition by Forecast_ID, Part_number order by DataAP )
into #IntermediateResults
from (values
( 1,1,10,2720,980,2170,430 ),
( 2,1,10,3440,427,2845,-168 ),
( 3,1,10,4160,696,3520,56 ),
( 4,1,10,4880,NULL,3970,-910 ),
( 5,1,10,5836,NULL,4956,-880 ),
( 6,1,10,6156,NULL,5356,-800 ),
( 7,1,10,7036,NULL,5756,-1280 ),
( 8,1,10,7756,NULL,6876,-880 ),
( 9,1,10,8156,NULL,6876,-1280 ),
( 10,1,10,8636,NULL,7836,-800 ),
( 11,2,10,2756,999,2179,422 ),
( 12,2,10,3411,469,2849,-93 ),
( 13,2,10,4113,664,3493,44 ),
( 14,2,10,4893,NULL,3973,-920 ),
( 15,2,10,5859,NULL,4997,-862 ),
( 16,1,20,2725,1002,2142,419 ),
( 17,1,20,3467,445,2890,-132 ),
( 18,1,20,4192,717,3473,-2 ),
( 19,1,20,4893,NULL,3963,-930 ),
( 20,1,20,5861,NULL,4928,-933 )
) val(DataAP,Forecast_ID,Part_number,Last_Forecasted_Demand,Delivered_Quantity,New_Forecasted_Demand,Demand_Change)
;
with rcte as (
Select
src.*
, [counter] = 1
, carry_over = case when Demand_Change > 0 then Demand_Change else 0 end
, Column_I_Need = case when Demand_Change < 0 then Demand_Change else 0 end
from #IntermediateResults src
where [row_number] = 1
union all
select
src.*
, [counter] = new_counter
, carry_over = case when sub_total > 0 then sub_total else 0 end
, Column_I_Need = case when sub_total < 0 then sub_total else 0 end
from rcte
cross apply ( Select
new_counter = [counter] + 1
) calc1
join #IntermediateResults src
on src.[row_number] = new_counter
and src.Forecast_ID = rcte.Forecast_ID
and src.Part_number = rcte.Part_number
cross apply ( Select
sub_total = rcte.carry_over + src.Demand_Change
) calc2
)
select * from rcte
order by DataAP