if object_id('p_splittime') is not null
drop proc p_splittime
go
create proc p_splittime
@begintime datetime,
@endtime datetime
as
set datefirst 1
declare @t table(begintime datetime,endtime datetime)
declare @begin varchar(10),@end varchar(10)
select @begin=开始时间,@end=开始时间
from tb
where datepart(dw,@begintime)=星期if @begintime<convert(datetime,convert(char(11),@begintime,120)+@begin,120) insert into @t
select @begintime,convert(datetime,convert(char(11),@begintime,120)+@begin,120)
union
select convert(datetime,convert(char(11),@begintime,120)+@begin,120),dateadd(day,1,convert(datetime,convert(char(11),@begintime,120)+@end,120))
union
select dateadd(day,1,convert(datetime,convert(char(11),@begintime,120)+@end,120)),@endtime
else
insert into @t
select @begintime,convert(datetime,convert(char(11),@begintime,120)+@end,120)
union
select convert(datetime,convert(char(11),@begintime,120)+@end,120),@endtimeselect * from @texec p_splittime '2008-12-16 04:00:00','2008-12-18 18:00:00'2008-12-16 04:00:00.000 2008-12-16 11:00:00.000
2008-12-16 11:00:00.000 2008-12-17 11:00:00.000
2008-12-17 11:00:00.000 2008-12-18 18:00:00.000exec p_splittime '2008-12-16 13:00:00','2008-12-18 18:00:00'2008-12-16 11:00:00.000 2008-12-18 18:00:00.000
2008-12-16 13:00:00.000 2008-12-16 11:00:00.000后面的id没用写。
稍微修改下,上面的稍有错误,另外加了id的处理
if object_id('p_splittime') is not null
drop proc p_splittime
go
create proc p_splittime
@begintime datetime,
@endtime datetime
as
set datefirst 1
declare @t table(begintime datetime,endtime datetime,id int)
declare @begin varchar(10),@end varchar(10)
select @begin=开始时间,@end=结束时间
from tb
where 星期=datepart(dw,@begintime)if @begintime<convert(datetime,convert(char(11),@begintime,120)+@begin,120) insert into @t
select @begintime,convert(datetime,convert(char(11),@begintime,120)+@begin,120),isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=@begin),0)
union
select convert(datetime,convert(char(11),@begintime,120)+@begin,120),dateadd(day,1,convert(datetime,convert(char(11),@begintime,120)+@end,120)),isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=@end),0)
union
select dateadd(day,1,convert(datetime,convert(char(11),@begintime,120)+@end,120)),@endtime,isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=right(convert(varchar(28),@endtime,120),8)),0)
else
insert into @t
select @begintime,dateadd(day,1,convert(datetime,convert(char(11),@begintime,120)+@end,120)),isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=@end),0)
union
select dateadd(day,1,convert(datetime,convert(char(11),@begintime,120)+@end,120)),@endtime,isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=right(convert(varchar(28),@endtime,120),8)),0)select * from @texec p_splittime '2008-12-16 04:00:00','2008-12-18 18:00:00'2008-12-16 04:00:00.000 2008-12-16 11:00:00.000 0
2008-12-16 11:00:00.000 2008-12-17 04:00:00.000 22
2008-12-17 04:00:00.000 2008-12-18 18:00:00.000 0exec p_splittime '2008-12-16 13:00:00','2008-12-18 18:00:00'2008-12-16 13:00:00.000 2008-12-17 04:00:00.000 22
2008-12-17 04:00:00.000 2008-12-18 18:00:00.000 0
@begintime='2008-12-16 01:00'
@endtime='2008-12-16 04:00'
insert into @tb values(11,1,'10:00','03:00',1)
insert into @tb values(22,2,'11:00','04:00',1)
insert into @tb values(33,3,'12:00','05:00',1)
insert into @tb values(44,7,'13:00','06:00',1)
set datefirst 1
declare @t table(begintime datetime,endtime datetime,id int)
declare @begintime datetime,@endtime datetime,@dotime datetime,@doont datetime,@doendt datetime
set @begintime='2008-12-13 03:00'
set @endtime='2008-12-17 14:00'set @dotime=dateadd(dd,-1,@begintime)declare @xq table (xq int,ldate varchar(20))
while cast(convert(varchar(10),@dotime,120) as datetime)<=cast(convert(varchar(10),@endtime,120) as datetime)
begin
insert @xq (xq,ldate) values (datepart(dw,@dotime),convert(varchar(10),@dotime,120))
set @dotime=dateadd(dd,1,@dotime)
endif exists (select 1 from @tb
where (星期 = datepart(dw,@begintime)
or (跨日=1 and 星期=datepart(dw,dateadd(dd,-1,@begintime))))
and @begintime >= (case when 星期=datepart(dw,@begintime) then convert(char(10),@begintime,120)+' '+开始时间
else convert(char(10),dateadd(dd,-1,@begintime),120)+' '+开始时间 end)
and
@begintime < (case when 星期=datepart(dw,@begintime) and 跨日=0 then convert(char(10),@begintime,120)+' '+结束时间
when 星期=datepart(dw,@begintime) and 跨日=1 then convert(char(10),dateadd(dd,1,@begintime),120)+' '+结束时间
else convert(char(10),@begintime,120)+' '+结束时间 end))
begin
select top 1 @doont=(select cast(ldate+' '+结束时间 as datetime) from @xq where xq=a.星期),
@doendt=case 跨日 when 1 then dateadd(dd,1,(select cast(ldate+' '+结束时间 as datetime) from @xq where xq=a.星期))
else (select cast(ldate+' '+结束时间 as datetime) from @xq where xq=a.星期) end
from @tb as a
where (星期 = datepart(dw,@begintime)
or (跨日=1 and 星期=datepart(dw,dateadd(dd,-1,@begintime))))
and @begintime >= (case when 星期=datepart(dw,@begintime) then convert(char(10),@begintime,120)+' '+开始时间
else convert(char(10),dateadd(dd,-1,@begintime),120)+' '+开始时间 end)
and
@begintime < (case when 星期=datepart(dw,@begintime) and 跨日=0 then convert(char(10),@begintime,120)+' '+结束时间
when 星期=datepart(dw,@begintime) and 跨日=1 then convert(char(10),dateadd(dd,1,@begintime),120)+' '+结束时间
else convert(char(10),@begintime,120)+' '+结束时间 end)
insert @t (begintime,endtime,id) values (@begintime,@doendt,1)
insert @t (begintime,endtime,id) values (@doendt,@endtime,1)
end
else
begin
select top 1 @doont=(select cast(ldate+' '+开始时间 as datetime) from @xq where xq=a.星期),
@doendt=case 跨日 when 1 then dateadd(dd,1,(select cast(ldate+' '+结束时间 as datetime) from @xq where xq=a.星期))
else (select cast(ldate+' '+结束时间 as datetime) from @xq where xq=a.星期) end
from @tb as a
where 星期 in (select xq from @xq)
and (select cast(ldate+' '+开始时间 as datetime) from @xq where xq=a.星期) >= @begintime
and (select cast(ldate+' '+开始时间 as datetime) from @xq where xq=a.星期) < @endtime
order by (select cast(ldate+' '+开始时间 as datetime) from @xq where xq=a.星期)
insert @t (begintime,endtime,id) values (@begintime,@doont,0)
insert @t (begintime,endtime,id) values (@doont,@doendt,1)
insert @t (begintime,endtime,id) values (@doendt,@endtime,0)
end
select * from @tset datefirst 7帮忙看一下,还不完全,如果日期跨度过大(超过一个星期),就有问题
-- Author: happyflystone
-- Date:2008-12-17 15:49:49
-------------------------------------- Test Data: TA
IF OBJECT_ID('TA') IS NOT NULL
DROP TABLE TA
Go
CREATE TABLE TA(id INT,星期 INT,开始时间 varchar(8),结束时间 varchar(8),跨日 INT)
Go
INSERT INTO TA
SELECT 11,1,'10:00','03:00',1 UNION ALL
SELECT 22,2,'11:00','04:00',1 UNION ALL
SELECT 33,3,'12:00','05:00',1 UNION ALL
SELECT 44,4,'13:00','06:00',1
GO
--Start
go
create proc pr_test(@s datetime,@e datetime)
as
begin
declare @ss varchar(8),@ee varchar(8),@id int,@k int select @id = id,@ss = 开始时间,@ee = 结束时间 ,@k= 跨日
from ta
where 星期 = datepart(wk,开始时间)+1
declare @t table(d1 datetime,d2 datetime,id int)
if right(convert(char(19),@s,120),8) < @ss+':00'
begin
insert @t select @s,left(convert(char(19),@s,120),11)+@ss,0
insert @t select left(convert(char(19),@s,120),11)+@ss,left(convert(char(19),case when @k = 1 then @s+1 else @s end,120),11)+@ee,@id
end
else
insert @t select @s,left(convert(char(19),@s,120),11)+@ee,@id
insert @t select left(convert(char(19),case when @k = 1 then @s+1 else @s end,120),11)+@ee,@e ,0
select * from @t
end
go
exec pr_test '2008-12-16 04:00:00','2008-12-18 18:00:00'
exec pr_test '2008-12-16 13:00:00','2008-12-18 18:00:00'
exec pr_test '2008-12-16 01:00:00','2008-12-18 18:00:00'drop proc pr_test
--Result:
/*
d1 d2 id
----------------------- ----------------------- -----------
2008-12-16 04:00:00.000 2008-12-16 11:00:00.000 0
2008-12-16 11:00:00.000 2008-12-17 04:00:00.000 22
2008-12-17 04:00:00.000 2008-12-18 18:00:00.000 0(3 行受影响)
d1 d2 id
----------------------- ----------------------- -----------
2008-12-16 13:00:00.000 2008-12-16 04:00:00.000 22
2008-12-17 04:00:00.000 2008-12-18 18:00:00.000 0(2 行受影响)d1 d2 id
----------------------- ----------------------- -----------
2008-12-16 01:00:00.000 2008-12-16 11:00:00.000 0
2008-12-16 11:00:00.000 2008-12-17 04:00:00.000 22
2008-12-17 04:00:00.000 2008-12-18 18:00:00.000 0(3 行受影响)*/
--End
2008-12-16 01:00:00.000 2008-12-16 11:00:00.000 0
2008-12-16 11:00:00.000 2008-12-17 04:00:00.000 22
2008-12-17 04:00:00.000 2008-12-16 04:00:00.000 0 应该是:
2008-12-16 01:00:00.000 2008-12-16 03:00:00.000 1
2008-12-16 03:00:00.000 2008-12-16 04:00:00.000 1
insert into @tb values(11,1,'10:00','03:00',1)
insert into @tb values(22,2,'11:00','04:00',1)
insert into @tb values(33,3,'12:00','05:00',1)
insert into @tb values(44,7,'13:00','06:00',1)
set datefirst 1
declare @t table(begintime datetime,endtime datetime,id int)
declare @begintime datetime,@endtime datetime,@dotime datetime,@doont datetime,@doendt datetime,@doid int
set @begintime='2008-12-12 01:00'
set @endtime='2008-12-16 04:00'set @dotime=dateadd(dd,-1,@begintime)declare @xq table (xq int,ldate varchar(20))
while cast(convert(varchar(10),@dotime,120) as datetime)<=cast(convert(varchar(10),@endtime,120) as datetime)
begin
if not exists (select 1 from @xq where xq=datepart(dw,@dotime))
insert @xq (xq,ldate) values (datepart(dw,@dotime),convert(varchar(10),@dotime,120))
set @dotime=dateadd(dd,1,@dotime)
endif exists (select 1 from @tb
where (星期 = datepart(dw,@begintime)
or (跨日=1 and 星期=datepart(dw,dateadd(dd,-1,@begintime))))
and @begintime >= (case when 星期=datepart(dw,@begintime) then convert(char(10),@begintime,120)+' '+开始时间
else convert(char(10),dateadd(dd,-1,@begintime),120)+' '+开始时间 end)
and
@begintime < (case when 星期=datepart(dw,@begintime) and 跨日=0 then convert(char(10),@begintime,120)+' '+结束时间
when 星期=datepart(dw,@begintime) and 跨日=1 then convert(char(10),dateadd(dd,1,@begintime),120)+' '+结束时间
else convert(char(10),@begintime,120)+' '+结束时间 end))
begin
select top 1 @doont=(select cast(ldate+' '+结束时间 as datetime) from @xq where xq=a.星期),
@doendt=case 跨日 when 1 then dateadd(dd,1,(select cast(ldate+' '+结束时间 as datetime) from @xq where xq=a.星期))
else (select cast(ldate+' '+结束时间 as datetime) from @xq where xq=a.星期) end,
@doid=id
from @tb as a
where (星期 = datepart(dw,@begintime)
or (跨日=1 and 星期=datepart(dw,dateadd(dd,-1,@begintime))))
and @begintime >= (case when 星期=datepart(dw,@begintime) then convert(char(10),@begintime,120)+' '+开始时间
else convert(char(10),dateadd(dd,-1,@begintime),120)+' '+开始时间 end)
and
@begintime < (case when 星期=datepart(dw,@begintime) and 跨日=0 then convert(char(10),@begintime,120)+' '+结束时间
when 星期=datepart(dw,@begintime) and 跨日=1 then convert(char(10),dateadd(dd,1,@begintime),120)+' '+结束时间
else convert(char(10),@begintime,120)+' '+结束时间 end)
insert @t (begintime,endtime,id) values (@begintime,@doendt,@doid)
insert @t (begintime,endtime,id) values (@doendt,@endtime,0)
end
else
begin
select top 1 @doont=(select cast(ldate+' '+开始时间 as datetime) from @xq where xq=a.星期),
@doendt=case 跨日 when 1 then dateadd(dd,1,(select cast(ldate+' '+结束时间 as datetime) from @xq where xq=a.星期))
else (select cast(ldate+' '+结束时间 as datetime) from @xq where xq=a.星期) end,
@doid=id
from @tb as a
where 星期 in (select xq from @xq)
and (select cast(ldate+' '+开始时间 as datetime) from @xq where xq=a.星期) >= @begintime
and (select cast(ldate+' '+开始时间 as datetime) from @xq where xq=a.星期) < @endtime
order by (select cast(ldate+' '+开始时间 as datetime) from @xq where xq=a.星期)
insert @t (begintime,endtime,id) values (@begintime,@doont,0)
insert @t (begintime,endtime,id) values (@doont,@doendt,@doid)
insert @t (begintime,endtime,id) values (@doendt,@endtime,0)
end
select * from @tset datefirst 7
不过太麻烦,还有没有好的办法?
id 星期 开始时间 结束时间 跨日 ....
11 1 10:00 03:00 1 ....
22 2 11:00 04:00 1 ....
33 3 12:00 05:00 1 ....
44 4 13:00 06:00 1 .... 输入:起始时间=2008-12-16 04:00:00 ;结束时间=2008-12-18 18:00:00
返回:
起始时间 结束时间 表id
2008-12-16 04:00:00 2008-12-16 11:00:00 0
2008-12-16 11:00:00 2008-12-17 04:00:00 22
2008-12-17 04:00:00 2008-12-18 18:00:00 0 那么你的2008-12-16 04:00:00 刚开始我的理解就是找时间差最小的的,那么就是11 1 10:00 03:00 1 .... 这样来分割的,那么开始的第一行就应该是从2008-12-16 03:00开始的。结果你给的数据确不一样。从你要的结果就可以看出来。(你表中的结束时间数据应该是第二天的时间吧)那么只能按照你给的结果写了,结果你后面反而要的是这种结果。而你后面的要求是exec pr_test '2008-12-16 01:00:00','2008-12-16 04:00:00'
应该是:
2008-12-16 01:00:00.000 2008-12-16 03:00:00.000 1
2008-12-16 03:00:00.000 2008-12-16 04:00:00.000 1 你自己把要求说不清楚,怎么帮你写
你自己把if那块修改下,找到时间差最小的哪个。然后再进行插入就可以了。
1、起始时间怎么确定的,@begin,@end是输入的参数,那么分割出来的时间段是从大于@begin的最小时间开始?即:
id 星期 开始时间 结束时间 跨日 ....
11 1 10:00 03:00 1 ....
22 2 11:00 04:00 1 ....
33 3 12:00 05:00 1 ....
44 4 13:00 06:00 1 ....
即起始时间=2008-12-16 04:00:00 ;结束时间=2008-12-18 18:00:00
那么大于2008-12-16 04:00:00 的最小时间就应该是 22 2 11:00 04:00 1 那么开始时间为这一行的开始时间
那么分割出来的数据就应该是
2008-12-16 04:00:00 2008-12-16 11:00:00 0
2008-12-16 11:00:00 2008-12-17 04:00:00 22
2008-12-17 04:00:00 2008-12-18 18:00:00 0
如果:起始时间='2008-12-16 01:00:00';结束时间='2008-12-16 04:00:00' 那么大于'2008-12-16 01:00:00'的最小时间应该是上一行的结束时间
11 1 10:00 03:00 1
那么分割出来的数据就
2008-12-16 01:00:00.000 2008-12-16 03:00:00.000 1
2008-12-16 03:00:00.000 2008-12-16 04:00:00.000 1
这个貌似是你要的分割方法吧,但确不满足你所要的--》 找到最靠近起始时间的符合记录
2、而你帖子中要的结果是--》找到最靠近起始时间的符合记录,把输入时间段切割开成几个时间段的表。
那么起始时间=2008-12-16 04:00:00 ;结束时间=2008-12-18 18:00:00 最靠近起始时间的11 1 10:00 03:00 1 ,即上一行的结束时间,时间差为1个小时,和下面的22 2 11:00 04:00 1 ,时间差为7个小时,那么就按照你帖子的说法,取最靠近起始时间的记录,那么起始时间为2008-12-16 03:00:00.000 .但这个好象不是你从数据的角度上得到的结果两种对时间的分割方法是不一样的,请楼主想说下你究竟要使用哪一种分割方法
insert into tb
select 11, 1, '10:00' , '03:00' , 1 union all
select 22, 2 , '11:00' , '04:00' , 1 union all
select 33 , 3 , '12:00' , '05:00' , 1 union all
select 44 , 4 , '13:00' , '06:00' , 1 union all
select 55 , 5 , '15:00' , '05:00' , 1 union all
select 66 , 6 , '13:00' , '06:00' , 1
godeclare @StartTime datetime,@EndTime datetime;
select @StartTime ='2008-12-10 04:00:00',@EndTime = '2008-12-18 9:00:00';declare @StartTime1 datetime,@EndTime1 datetime,@TempTime datetime,@WeekDay int;
--把时间跨度向前增加一周,向后增加一周
select @StartTime1 =dateadd(week,-1,@StartTime),@EndTime1 =dateadd(week,1,@EndTime);declare @tabTimes table(时间点 datetime,类型 varchar(10),id int);set @TempTime =@StartTime1 ;
while @TempTime <= @EndTime1
begin
set @WeekDay = datepart(weekday,@TempTime)-1;
if @WeekDay <=1 set @WeekDay =7;
insert into @tabTimes select convert(varchar(12),@TempTime,101) + ' ' + 起始时间,'起始时间',id
from tb where 星期 = @WeekDay;
insert into @tabTimes select convert(varchar(12),dateadd(day,跨日,@TempTime),101) + ' ' + 结束时间,'结束时间',id
from tb where 星期 = @WeekDay;
set @TempTime = dateadd(day,1,@TempTime);
enddeclare @date datetime,@type varchar(10),@id int,@id1 int,@date1 datetime;
declare @tabResult table(起始时间 datetime,结束时间 datetime,表id int);declare cur cursor for select 时间点,类型,id from @tabTimes where 时间点 >= @StartTime and 时间点 <= @EndTime
order by 时间点;
open cur;
fetch cur into @date,@type,@id;
while @@fetch_status =0
begin
if @date1 is null
set @date1 = @StartTime;
if @type = '起始时间'
set @id1 =0;
else
set @id1 = @id;
insert into @tabResult values (@date1,@date,@id1);
set @date1 = @date ;
fetch cur into @date,@type,@id;
end
close cur;
deallocate cur;if @date1 is null
set @date1 = @StartTime;if @type = '结束时间'
set @id =0;
insert into @tabResult values (@date1,@EndTime,@id);select * from @tabResultgo
drop table tb结果:
2008-12-10 04:00:00.000 2008-12-10 04:00:00.000 22
2008-12-10 04:00:00.000 2008-12-10 12:00:00.000 0
2008-12-10 12:00:00.000 2008-12-11 05:00:00.000 33
2008-12-11 05:00:00.000 2008-12-11 13:00:00.000 0
2008-12-11 13:00:00.000 2008-12-12 06:00:00.000 44
2008-12-12 06:00:00.000 2008-12-12 15:00:00.000 0
2008-12-12 15:00:00.000 2008-12-13 05:00:00.000 55
2008-12-13 05:00:00.000 2008-12-13 13:00:00.000 0
2008-12-13 13:00:00.000 2008-12-14 06:00:00.000 66
2008-12-14 06:00:00.000 2008-12-16 11:00:00.000 0
2008-12-16 11:00:00.000 2008-12-17 04:00:00.000 22
2008-12-17 04:00:00.000 2008-12-17 12:00:00.000 0
2008-12-17 12:00:00.000 2008-12-18 05:00:00.000 33
2008-12-18 05:00:00.000 2008-12-18 09:00:00.000 0
应该是: id 星期 开始时间 结束时间 跨日 ....
11 1 10:00 03:00 1 ....
22 2 11:00 04:00 1 ....
33 3 12:00 05:00 1 ....
44 4 13:00 06:00 1 .... 输入起始时间、结束时间后
1、查找表中是否有包含起始时间的时间段,如果有那就取这个时间段,结束时间超出部分就做为另一个时间段,不管超出部分有多少时间,也不管超出部分是否满足表中的其它条件,都做为一个时间段。
如果:起始时间='2008-12-16 01:00:00';结束时间='2008-12-18 04:00:00' 那就符合这个条件,
所以结果是2008-12-16 01:00:00.000 2008-12-16 03:00:00.000 11 (符合条件的时间段)
2008-12-16 03:00:00.000 2008-12-18 04:00:00.000 0 (做为一个时间段)
2、如果起始时间没有包含在表中的任何一个时间段内,那就取表中的开始时间大于输入的起始时间,且最靠近起始时间的时间段,多余部分切割成两个时间段,同样这两个时间段不管有多少时间,也不管是否满足表中的其它条件,都只做为两个时间段。
如果:起始时间=2008-12-16 04:00:00 ;结束时间=2008-12-19 18:00:00
结果就是:2008-12-16 04:00:00 2008-12-16 11:00:00 0 (做为一个时间段)
2008-12-16 11:00:00 2008-12-17 04:00:00 22 (符合条件的时间段)
2008-12-17 04:00:00 2008-12-19 18:00:00 0 (做为一个时间段)我的文字表达比较差,希望大家见谅。:)
create proc p ( @begindate datetime,@enddate datetime)
as
declare @t table(id int, 星期 int, 开始时间 varchar(20), 结束时间 varchar(20), 跨日 int)
insert @t
select 11 ,1 ,'10:00','03:00', 1 union
select 22 ,2 ,'11:00','04:00', 1 union
select 33 ,3 ,'12:00','05:00', 1 union
select 44 ,4 ,'13:00','06:00', 1 declare @星期 int
select @星期=case when datepart(weekday,@begindate)-1=0 then 7 else datepart(weekday,@begindate)-1 endselect id,星期, 时间=convert(varchar(10),dateadd(day,星期-@星期,@begindate),20)+' '+开始时间+':00','开始时间' 类别 into #t from @t
union
select id,星期,convert(varchar(10),dateadd(day,星期-@星期+跨日,@begindate),20)+' '+结束时间+':00','结束时间' from @t declare @t1 table (fid int identity(1,1),id int,时间 datetime,类别 varchar(10))
insert @t1
select id=case when 类别='结束时间' then id else 0 end,时间,类别 from #t where 时间 between @begindate and
(select top 1 时间 from #t where 类别='结束时间' and 时间>=@begindate order by 时间)
union
select 0,@begindate,'开始'
union
select 0,@enddate,'结束' order by 时间select 开始时间=时间 ,
结束时间=(select 时间 from @t1 where fid=t.fid+1),
表id=(select id from @t1 where fid=t.fid+1)
from @t1 t where fid<(select max(fid) from @t1)GO
exec p '2008-12-16 01:00:00','2008-12-18 04:00:00'
/*
开始时间 结束时间 表id
------------------------------------------------------ ------------------------------------------------------ -----------
2008-12-16 01:00:00.000 2008-12-16 03:00:00.000 11
2008-12-16 03:00:00.000 2008-12-18 04:00:00.000 0
*/
exec p '2008-12-16 04:00:00','2008-12-19 18:00:00'
/*
开始时间 结束时间 表id
------------------------------------------------------ ------------------------------------------------------ -----------
2008-12-16 04:00:00.000 2008-12-16 11:00:00.000 0
2008-12-16 11:00:00.000 2008-12-17 04:00:00.000 22
2008-12-17 04:00:00.000 2008-12-19 18:00:00.000 0
*/
2008-12-16 03:00:00.000 2008-12-18 04:00:00.000 0 应该得到:
2008-12-16 03:00:00.000 2008-12-16 11:00:00.000 0
2008-12-16 11:00:00.000 2008-12-17 04:00:00.000 11
2008-12-17 04:00:00.000 2008-12-18 04:00:00.000 0
我理解你的逻辑应该是:
if 开始时间<上一行的结束时间
if 结束时间<=当天的结束时间
时间分割成: 开始时间,上一行的结束时间
上一行的结束时间,当天的开始时间
当天的开始时间,结束时间
else
时间分割成: 开始时间,上一行的结束时间
上一行的结束时间,当天的开始时间
当天的开始时间,当天的结束时间
当天的结束时间,结束时间
else if 开始时间<当天开始时间
if 结束时间<=当天的结束时间
时间分割成: 开始时间,当天的开始时间
当天的开始时间,结束时间
else
时间分割成: 开始时间,当天的开始时间
当天的开始时间,当天的结束时间
当天的结束时间,结束时间
else
if 结束时间<=当天的结束时间
时间分割成: 开始时间, 结束时间
else
时间分割成: 开始时间,当天的结束时间
当天的结束时间,结束时间在这次的修改中,对于后面的id没有进行处理,有些地方为0,主要是为了和表变量中的列相对应。另外一个原因是
exec pr_test '2008-12-16 01:00:00','2008-12-16 04:00:00' 应该是:
2008-12-16 01:00:00.000 2008-12-16 03:00:00.000 1 --这块的1是怎么来的,你表中似乎没有id为1的数据
2008-12-16 03:00:00.000 2008-12-16 04:00:00.000 1 if object_id('p_splittime') is not null
drop proc p_splittime
go
create proc p_splittime
@begintime datetime,
@endtime datetime
as
set datefirst 1
declare @t table(begintime datetime,endtime datetime,id int)
declare @begin varchar(10),@end varchar(10),@lastend varchar(10)
select @begin=开始时间,@end=结束时间
from tb
where 星期=datepart(dw,@begintime)
select @lastend=结束时间
from tb
where 星期=datepart(dw,dateadd(day,-1,@begintime))if @begintime<convert(datetime,convert(char(11),@begintime,120)+@lastend,120) if @endtime<=convert(datetime,convert(char(11),@begintime,120)+@end,120)
insert into @t
select @begintime,convert(datetime,convert(char(11),@begintime,120)+@lastend,120),isnull((select id from tb where 星期=datepart(dw,@begintime)-1 and 结束时间=@lastend),0)
union
select convert(datetime,convert(char(11),@begintime,120)+@lastend,120),@endtime,isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=@end),0)
else
insert into @t
select @begintime,convert(datetime,convert(char(11),@begintime,120)+@lastend,120),isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=@begin),0)
union
select convert(datetime,convert(char(11),@begintime,120)+@lastend,120),convert(datetime,convert(char(11),@begintime,120)+@begin,120),0
union
select convert(datetime,convert(char(11),@begintime,120)+@begin,120),dateadd(day,1,convert(datetime,convert(char(11),@begintime,120)+@end,120)),isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=@end),0)
union
select dateadd(day,1,convert(datetime,convert(char(11),@begintime,120)+@end,120)),@endtime,isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=right(convert(varchar(28),@endtime,120),8)),0)
else if @begintime<convert(datetime,convert(char(11),@begintime,120)+@begin,120)
if @endtime<convert(datetime,convert(char(11),@begintime,120)+@end,120)
insert into @t
select @begintime,convert(datetime,convert(char(11),@begintime,120)+@begin,120),0
union
select convert(datetime,convert(char(11),@begintime,120)+@begin,120),@endtime,0
else
insert into @t
select @begintime,convert(datetime,convert(char(11),@begintime,120)+@begin,120),isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=@begin),0)
union
select convert(datetime,convert(char(11),@begintime,120)+@begin,120),dateadd(day,1,convert(datetime,convert(char(11),@begintime,120)+@end,120)),isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=@end),0)
union
select dateadd(day,1,convert(datetime,convert(char(11),@begintime,120)+@end,120)),@endtime,isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=right(convert(varchar(28),@endtime,120),8)),0)
else
if @endtime<convert(datetime,convert(char(11),@begintime,120)+@end,120)
insert into @t
select @begintime,@endtime,0
else
insert into @t
select @begintime,dateadd(day,1,convert(datetime,convert(char(11),@begintime,120)+@end,120)),isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=@end),0)
union
select dateadd(day,1,convert(datetime,convert(char(11),@begintime,120)+@end,120)),@endtime,isnull((select id from tb where 星期=datepart(dw,@begintime) and 结束时间=right(convert(varchar(28),@endtime,120),8)),0)
select * from @t
全部改为if @endtime<=convert(datetime,convert(char(11),@begintime,120)+@end,120)结果:
exec p_splittime '2008-12-16 04:00:00','2008-12-18 18:00:00'2008-12-16 04:00:00.000 2008-12-16 11:00:00.000 0
2008-12-16 11:00:00.000 2008-12-17 04:00:00.000 22
2008-12-17 04:00:00.000 2008-12-18 18:00:00.000 0exec p_splittime '2008-12-16 13:00:00','2008-12-18 18:00:00'2008-12-16 13:00:00.000 2008-12-17 04:00:00.000 22
2008-12-17 04:00:00.000 2008-12-18 18:00:00.000 0exec p_splittime '2008-12-16 01:00:00','2008-12-16 04:00:00' 2008-12-16 01:00:00.000 2008-12-16 03:00:00.000 11
2008-12-16 03:00:00.000 2008-12-16 04:00:00.000 22