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

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,ee

drop 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 行受影响)