日期时间段分割处理！
根据日期时间段，得到每周的日期时间段。
例如：
数据:
001 , 2007/04/16 , 2007/04/29
002 , 2007/04/23 , 2007/04/29
得到数据：
001 , 2007/04/16 , 2007/04/22
001 , 2007/04/23 , 2007/04/29
002 , 2007/04/23 , 2007/04/29
[解决办法]
select id ,StartDate, '2007/04/22 ' as EndDate
from table1
when StartDate < '2007/04/23 ' and EndDate> '2007/04/22 '
union
select id , '2007/04/23 ' as StartDate, EndDate
from table1
when StartDate < '2007/04/23 ' and EndDate> '2007/04/22 '
union
select id ,StartDate, EndDate
from table1
when StartDate> = '2007/04/23 ' or EndDate <= '2007/04/22 '
[解决办法]
目前除了用游标，还没想到别的思路。。。
[解决办法]
create function getTableG(@a varchar(3),@s smalldatetime,@e smalldatetime)
returns @t table(m varchar(3),n smalldatetime,p smalldatetime)
as
begin
declare @y table (a int identity(0,1),c smalldatetime,d smalldatetime,b char(1))
insert @y(c,d,b) select @s,null,null from syscolumns
update @y set d=dateadd(day,a,c)
delete from @y where d> @e
update @y set b= '1 ' where datepart(dw,d)=2
update @y set b= '7 ' where datepart(dw,d)=1
update @y set b= '1 ' where d=@s and b is null
update @y set b= '7 ' where d=@e and b is null
delete from @y where b is null
if exists(select 1 from @y where b=7 and d=@s)
insert @t select @a,@s,@s
if exists(select 1 from @y where b=1 and d=@e)
begin
insert @t select @a,@e,@e
insert @t select @a,d,null from @y where b=1 and d <> @e
end
else
insert @t select @a,d,null from @y where b=1
update @t set p=d from @t,@y where datediff(day,n,d)=6 and p is null
update @t set p=@e where p is null
return
end
go
declare @a table(a varchar(3),s smalldatetime,e smalldatetime)
insert @a select '001 ', '2007/04/16 ', '2007/04/29 '
union all select '002 ' , '2007/04/23 ', '2007/04/29 '
union all select '003 ' , '2007/04/24 ', '2007/05/29 '
declare @i varchar(3),@o smalldatetime,@e smalldatetime
declare @g table(a varchar(3),s smalldatetime,e smalldatetime)
declare cur cursor for
select * from @a order by a
open cur
fetch next from cur into @i,@o,@e
while @@fetch_status=0
begin
insert @g select * from dbo.gettableG(@i,@o,@e)
fetch next from cur into @i,@o,@e
end
close cur
deallocate cur
select * from @g
[解决办法]
如果統計用的話,不防建一個每年的周時間表.
第十六周 16 2007-04-16 00:00:00 2007-04-22 00:00:00
........ 17 2007-04-23 00:00:00 2007-04-29 00:00:00
select a.name,b.startdate,b.enddate
from tmpTestdate a,tmpWeek b
where a.bgndate=b.startdate or a.enddate=b.enddate
result:
001 2007-04-16 00:00:00 2007-04-22 00:00:00
001 2007-04-23 00:00:00 2007-04-29 00:00:00
002 2007-04-23 00:00:00 2007-04-29 00:00:00
這樣的代碼就簡單多拉:))
[解决办法]
Up
[解决办法]
對於比較特殊的情況可以用下法.(你的數據特殊)
Name BgnDate EndDate
---- ------------------------------------------------------ ------------------------------------------------------
001 2007-04-16 00:00:00 2007-04-29 00:00:00
002 2007-04-23 00:00:00 2007-04-29 00:00:00
003 2007-04-18 00:00:00 2007-05-03 00:00:00
(3 row(s) affected)
16 2007-04-16 00:00:00 2007-04-22 00:00:00
17 2007-04-23 00:00:00 2007-04-29 00:00:00
18 2007-04-30 00:00:00 2007-05-06 00:00:00
第三條是我加進去的記錄．．日期為任一值．
select a.name,b.startdate,b.enddate
from tmpTestdate a,tmpWeek b
where (a.bgndate> =b.startdate and a.bgndate <=b.enddate)
or (a.bgndate <=b.startdate and a.enddate> =b.enddate)
or (a.enddate> =b.startdate and a.enddate <=b.enddate)
order by name
result:
001 2007-04-16 00:00:00 2007-04-22 00:00:00
001 2007-04-23 00:00:00 2007-04-29 00:00:00
002 2007-04-23 00:00:00 2007-04-29 00:00:00
003 2007-04-16 00:00:00 2007-04-22 00:00:00
003 2007-04-23 00:00:00 2007-04-29 00:00:00
003 2007-04-30 00:00:00 2007-05-06 00:00:00
