还是先前的问题：有两个时间点，确定他们与一个给定的时间段[a,b]重叠的部分有多长？
/*任意给定两个时间点start_time,end_time（两个时间点可能同一天，也可能不同），另外给定一个时间区段[ss,ee],其中时间区段[ss,ee]只有小时和分钟（无日期）。要求：找出从start_time开始到end_time结束这段时间内，有多长时间是在[ss,ee]里面的？（最终时长以分钟计算） 跨天的有点麻烦。。*/ --建表--对于ss和ee列，数字0表示时间00:00,数字1000表示10:00,数字400表示04:00,数字2400表示时间24:00（次日的00:00），--其他类似create table temp(start_time datetime,end_time datetime,ss int,ee int)goinsert into temp values('2011-12-20 22:10:00','2011-12-20 23:15:00',1000,2400);insert into temp values('2011-11-01 00:15:00','2011-11-01 05:13:00',0,200);insert into temp values('2011-11-01 05:15:00','2011-11-01 06:13:00',400,600);insert into temp values('2011-10-20 23:45:00','2011-10-21 05:20:00',2200,2400);insert into temp values('2011-10-20 23:45:00','2011-10-21 08:20:00',0,200);insert into temp values('2011-10-20 23:45:00','2011-10-23 08:20:00',2200,2400);go/*结果应该是：（period单位：分钟）period 65 45 58 15 120 150*/create table temp(start_time datetime,end_time datetime,ss int,ee int)goinsert into temp values('2011-12-20 22:10:00','2011-12-20 23:15:00',1000,2400);insert into temp values('2011-11-01 00:15:00','2011-11-01 05:13:00',0,200);insert into temp values('2011-11-01 05:15:00','2011-11-01 06:13:00',400,600);insert into temp values('2011-10-20 23:45:00','2011-10-21 05:20:00',2200,2400);insert into temp values('2011-10-20 23:45:00','2011-10-21 08:20:00',0,200);insert into temp values('2011-10-20 23:45:00','2011-10-23 08:20:00',2200,2400);go;with ach as( select a.start_time,a.end_time,ss,ee, dateadd(dd,b.number, (case when b.number <> 0 then convert(datetime,convert(varchar(8),a.start_time,112)) else a.start_time end)) fact_start from temp a,master..spt_values b where b.[type] = 'p' and b.number between 0 and datediff(dd,a.start_time,a.end_time)),art as( select start_time,end_time,ss,ee, convert(datetime,convert(varchar(11),fact_start,120)+ (case when ss/100 in (24,0) then '00' else right(100+ss/100,2) end)+':' +right(100+ss%100,2)) new_start, dateadd(dd,(case when ee/100=24 then 1 else 0 end), convert(datetime,convert(varchar(11),fact_start,120)+ (case when ee/100 in (24,0) then '00' else right(100+ee/100,2) end)+':' +right(100+ee%100,2))) new_end from ach)select start_time,end_time,ss,ee, sum(case when new_start between start_time and end_time or start_time between new_start and new_end then (datediff(mi,(case when start_time >= new_start then start_time else new_start end), (case when end_time <= new_end then end_time else new_end end))) else 0 end) miufrom artgroup by start_time,end_time,ss,eedrop table temp/********************************start_time end_time ss ee miu----------------------- ----------------------- ----------- ----------- -----------2011-10-20 23:45:00.000 2011-10-21 05:20:00.000 2200 2400 152011-10-20 23:45:00.000 2011-10-21 08:20:00.000 0 200 1202011-10-20 23:45:00.000 2011-10-23 08:20:00.000 2200 2400 2552011-11-01 00:15:00.000 2011-11-01 05:13:00.000 0 200 1052011-11-01 05:15:00.000 2011-11-01 06:13:00.000 400 600 452011-12-20 22:10:00.000 2011-12-20 23:15:00.000 1000 2400 65(6 行受影响)