用特定条件减去时间循环中的日期

Question

我有这样的案子。事实表如下所示：

ID  orders end_date         code
1   1      2022-10-10       ZTTS
2   1      2022-10-10       ZTTS12
3   1      2022-10-10       ZTTS
4   1      2022-10-10       ZTTS
5   1      2022-10-10       ZTTS12
6   1      2022-10-10       ZTTS
7   2      2022-10-10       ZTTS
8   2      2022-10-10       ZTTS
9   2      2022-10-10       ZTTS
10  2      2022-10-10       ZTTS
11  2      2022-10-10       ZTTS12
12  2      2022-10-10       ZTTS

现在，企业希望重新安排这些订单，并查看end_date的具体情况。对于代码ZTTS，它应该从结束日期开始计算2天，从结束时开始，对于ZTTS12 - 12天。

预期结果如下

ID  orders end_date         code
1   1      2022-09-10       ZTTS
2   1      2022-09-12       ZTTS12
3   1      2022-09-24       ZTTS
4   1      2022-09-26       ZTTS
5   1      2022-09-28       ZTTS12
6   1      2022-10-10       ZTTS
7   2      2022-09-20       ZTTS
8   2      2022-09-22       ZTTS
9   2      2022-09-24       ZTTS
10  2      2022-09-26       ZTTS
11  2      2022-09-28       ZTTS12
12  2      2022-10-10       ZTTS

我在这段时间里一直想这么做

declare @data_zlecenie date
declare @days2 int = -2
declare @days12 int = -12
declare @iterator int = 1
declare @T int = (select MAX(id)-1 from dbo.tests)

declare @kod1 varchar(10) = 'ZTTS'
declare @kod2 varchar(10) = 'ZTTS12'

while @T >= @iterator /*1*/
begin 
            
    update dbo.tests set end_date = dateadd(day, @days2, end_date ) where ID = @T 
            
    set @T = @T - 1
                
    set @days2 = @days2 - 2 
                
end;

它可以工作，但只适用于减去2或12和所有订单(不划分为订单)。也许有更简单的方法来做这件事？谢谢

Answer 1

为什么不使用case子句？有点像

update dbo.test set end_date=dateadd(day,case 
when code='ZTTS' then -2
when code='ZTTS12' then -12
else 0
end,end_date)