ta
id MyType Adate MyEndDate MySign
1 R 2007-8-25 0
2 F 2007-8-2 0
3 F 2007-8-26 0
4 F 2007-8-19 0
5 R 2007-8-19 0
6 R 2007-8-17
上表，要用一个update语句来更新myEndDate
更新原则：
当MyType='R' 时, MyEndDate 为 Adate之后的第一个星期天
当MyType='F' 时, MyEndDate 为 Adate之后的第三个星期天
当Adate本身为星期天时，不更新MyEndDate ，更 MySign=1
请问如何实现？？
------解决方案--------------------
- SQL code
--原始数据：@TAdeclare @TA table(id int,MyType varchar(1),Adate datetime,MyEndDate datetime,MySign int)insert @TAselect 1,'R','2007-8-25',null,0 union allselect 2,'F','2007-8-2',null,0 union allselect 3,'F','2007-8-26',null,0 union allselect 4,'F','2007-8-19',null,0 union allselect 5,'R','2007-8-19',null,0 union allselect 6,'R','2007-8-1',null,0update @TA setMyEndDate= case (@@datefirst-1+datepart(weekday,Adate))%7 when 0 then MyEndDate else dateadd(day,(case MyType when 'R' then 1 else 3 end)*7-(@@datefirst-1+datepart(weekday,Adate))%7,ADate) end,MySign= case (@@datefirst-1+datepart(weekday,Adate))%7 when 0 then 1 else MySign endselect * from @TA/*id MyType Adate MyEndDate MySign ----------- ------ ------------------------------------------------------ ------------------------------------------------------ ----------- 1 R 2007-08-25 00:00:00.000 2007-08-26 00:00:00.000 02 F 2007-08-02 00:00:00.000 2007-08-19 00:00:00.000 03 F 2007-08-26 00:00:00.000 NULL 14 F 2007-08-19 00:00:00.000 NULL 15 R 2007-08-19 00:00:00.000 NULL 16 R 2007-08-01 00:00:00.000 2007-08-05 00:00:00.000 0（所影响的行数为 6 行）*/
------解决方案--------------------
不好意思,應該是8-
update ta set MyEndDate = (CASE WHEN datepart(dw,Adate)= 1 then MyEndDate
ELSE
CASE WHEN MyType = 'R ' THEN DATEADD(dd,8-DATEPART(dw,Adate),Adate)
CASE WHEN MyType = 'F ' THEN DATEADD(dd,14+8-DATEPART(dw,Adate),Adate) END
END ),
MySign = (CASE WHEN datepart(dw,Aate)= 1 THEN 1 ELSE mySign END )
------解决方案--------------------
- SQL code
declare @TA table(id int,MyType varchar(1),Adate datetime,MyEndDate datetime,MySign int)insert @TAselect 1,'R','2007-8-25',null,0 union allselect 2,'F','2007-8-2',null,0 union allselect 3,'F','2007-8-26',null,0 union allselect 4,'F','2007-8-19',null,0 union allselect 5,'R','2007-8-19',null,0 union allselect 6,'R','2007-8-1',null,0update tset [MyEndDate]=case when MyType='R' and (DATEPART(Weekday,Adate)+@@DATEFIRST-1)%7<7 then DATEADD(Day,7-(DATEPART(Weekday,Adate)+@@DATEFIRST-1)%7,Adate) when MyType='F' and (DATEPART(Weekday,Adate)+@@DATEFIRST-1)%7<7 then DATEADD(Day,7-(DATEPART(Weekday,Adate)+@@DATEFIRST-1)%7+14,Adate) else Adate end from @ta t select * from @ta----（所影响的行数为 6 行）（所影响的行数为 6 行）id MyType Adate MyEndDate MySign ----------- ------ ------------------------------------------------------ ------------------------------------------------------ ----------- 1 R 2007-08-25 00:00:00.000 2007-08-26 00:00:00.000 02 F 2007-08-02 00:00:00.000 2007-08-19 00:00:00.000 03 F 2007-08-26 00:00:00.000 2007-09-16 00:00:00.000 04 F 2007-08-19 00:00:00.000 2007-09-09 00:00:00.000 05 R 2007-08-19 00:00:00.000 2007-08-26 00:00:00.000 06 R 2007-08-01 00:00:00.000 2007-08-05 00:00:00.000 0（所影响的行数为 6 行）