还是先前的问题：有两个时间点，确定他们与一个给定的时间段[a,b]重叠的部分有多长？

Question

/*
任意给定两个时间点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)
go
insert 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
*/

Answer 1

之前数据看歪了，正确结果为：
period
 65
 105
 45
 15
 120
 150

Answer 2

create table temp(start_time datetime,end_time datetime,ss int,ee int)
go
insert 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) miu
from art
group 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        15
2011-10-20 23:45:00.000 2011-10-21 08:20:00.000 0           200         120
2011-10-20 23:45:00.000 2011-10-23 08:20:00.000 2200        2400        255
2011-11-01 00:15:00.000 2011-11-01 05:13:00.000 0           200         105
2011-11-01 05:15:00.000 2011-11-01 06:13:00.000 400         600         45
2011-12-20 22:10:00.000 2011-12-20 23:15:00.000 1000        2400        65(6 行受影响)