日期问题 ------------- 应该算简单吧
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 行）
[解决办法]
declare @TA table(id int,MyType varchar(1),Adate datetime,MyEndDate datetime,MySign int)
insert @TA
select 1,'R','2007-8-25',null,0 union all
select 2,'F','2007-8-2',null,0 union all
select 3,'F','2007-8-26',null,0 union all
select 4,'F','2007-8-19',null,0 union all
select 5,'R','2007-8-19',null,0 union all
select 6,'R','2007-8-1',null,0
update @TA
SET MyEndDate=case when MyType='R' then dateadd(day,(8-datepart(weekday,Adate)),Adate)
WHEN MyType='F' then dateadd(day,(8-datepart(weekday,Adate))+14,Adate)
end
--当Adate 为星期天时，MyEndDate更新为空，MySign更新为1
update @TA
SET MyEndDate=CASE WHEN datepart(weekday,Adate)=1 THEN '' ELSE MyEndDate END,
MySign=case when datepart(weekday,Adate)=1 then 1 else 0 end
SELECT * FROM @TA
id MyType Adate MyEndDate MySign
----------- ------ ------------------------------------------------------ ------------------------------------------------------ -----------
1 R 2007-08-25 00:00:00.000 2007-08-26 00:00:00.000 0
2 F 2007-08-02 00:00:00.000 2007-08-19 00:00:00.000 0
3 F 2007-08-26 00:00:00.000 1900-01-01 00:00:00.000 1
4 F 2007-08-19 00:00:00.000 1900-01-01 00:00:00.000 1
5 R 2007-08-19 00:00:00.000 1900-01-01 00:00:00.000 1
6 R 2007-08-01 00:00:00.000 2007-08-05 00:00:00.000 0
（所影响的行数为 6 行）
[解决办法]
create table ta(id int,MyType varchar(10),Adate datetime,MyEndDate datetime,MySign int)
insert into ta (id,MyType,Adate,MySign) select 1,'R','2007-8-25',0
insert into ta (id,MyType,Adate,MySign) select 2,'F','2007-8-2',0
insert into ta (id,MyType,Adate,MySign) select 3,'F','2007-8-26',0
insert into ta (id,MyType,Adate,MySign) select 4,'F','2007-8-19',0
insert into ta (id,MyType,Adate,MySign) select 5,'R','2007-8-19',0
insert into ta (id,MyType,Adate) select 6,'R','2007-8-17'
update ta set MySign=(case when datepart(dw,Adate)=1 then 1 else 0 end),
MyEndDate=(case when MyType='R' and datepart(dw,Adate)<>1 then dateadd(dd,8-datepart(dw,Adate),Adate)
when MyType='F' and datepart(dw,Adate)<>1 then dateadd(dd,22-datepart(dw,Adate),Adate)
else null end)