生成指定条件的记录
各位老师，请教一个问题：
定义上班时间段:上午8:00:00-12:00:00,下午:13:30:00-17:30:00,晚上18:30-21:30,
给定一个起始时间，如定义变量@，@='2013-11-07 09:35:00',设定一分钟数量，如@b=35，我的问题是在@a代表的时间上每次迭加@b代表的分钟数，若超过上班时间段，则要将剩余时间加到下上班进间段中，
要避开中午12:00到13:00,下午17:30-18:30,晚上21:30以后的时间,
例子：
次数 结果时间
----------------------------------
1 2013-11-07 10:10:00
2 2013-11-07 10:45:00
3 2013-11-07 11:20:00
4 2013-11-07 11:55:00 --加上5,剩余加到下一时间段
5 2013-11-07 14:00:00 ---从13:30加上30分钟
6 2013-11-07 14:35:00
7 2013-11-07 15:10:00
8 2013-11-07 15:45:00
9 2013-11-07 16:20:00
10 2013-11-07 16:55:00
11 2013-11-07 17:30:00
12 2013-11-07 19:10:00 ---从18:30加上35分钟
13 2013-11-07 19:45:00
14 2013-11-07 20:20:00
15 2013-11-07 20:55:00
16 2013-11-07 21:30:00
17 2013-11-08 08:35:00 ---第二天从8:00开始
请给出生成纪录的SQL语句，谢谢！
[解决办法]
declare @startdate datetime='2013-11-07 10:10:00'
declare @incre int =35;
declare @preDate datetime;
declare @i int=0;
if object_id('tempdb..#t') is not null drop table #t;
create table #t(id int,EventDate datetime)
set @preDate=@startdate;
while ( @i<100)
begin
if datepart(hour,dateadd(minute,@incre,@preDate))*100+datepart(minute,dateadd(minute,@incre,@preDate)) between 1201 and 1329
begin
insert into #t values(@i+1,dateadd(minute,@incre+90,@preDate));
set @preDate=dateadd(minute,@incre+90,@preDate)
end
else if datepart(hour,dateadd(minute,@incre,@preDate))*100+datepart(minute,dateadd(minute,@incre,@preDate)) between 1731 and 1829
begin
insert into #t values(@i+1,dateadd(minute,@incre+60,@preDate));
set @preDate=dateadd(minute,@incre+60,@preDate)
end
else if datepart(hour,dateadd(minute,@incre,@preDate))*100+datepart(minute,dateadd(minute,@incre,@preDate))>2130
begin
insert into #t values(@i+1,dateadd(minute,@incre+630,@preDate));
set @preDate=dateadd(minute,@incre+630,@preDate)
end
else
begin
insert into #t values(@i+1,dateadd(minute,@incre,@preDate));
set @preDate=dateadd(minute,@incre,@preDate)
end
set @i=@i+1;
end
select *
from #t
