/*
要求：对于任意两个时间(可能同一天，也可能间隔一天)，计算它们在给定时间段[ss,ee]里的时间长度。。

下面的自定义函数基本思路是将start_time和end_time截取小时分钟转为int形式，再与ss，ee做比较，分类讨论。

现在的问题是：不能实现间隔一天的情况，如何更改呢？
*/

if object_id('temp') is not null
    drop table temp
go

--ss和ee列，以0到2400（间隔为100）依次表示00:00到24: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,700);
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 17:45:00','2011-10-21 08:20:00',2200,2400);
go

if object_id('dbo.fgetperiod') is not null
    drop function dbo.fgetperiod
go

create function fgetperiod(@start_time_0 datetime,@end_time_0 datetime,@ss int,@ee int)
returns bigint 
as 
begin
declare @period int,@start_time int,@end_time int

select @start_time=replace(substring(convert(char,@start_time_0,120),charindex(':',@start_time_0)-2,5),':','')
select @end_time=replace(substring(convert(char,@end_time_0,120),charindex(':',@end_time_0)-2,5),':','')

if @start_time<=@ss 
begin
    if @end_time<=@ee
    begin
        if @end_time>=@ss
            select @period=(@end_time/100*60+@end_time%100)-(@ss/100*60+@ss%100)
        else
            select @period=0            
    end
    else 
        select @period=(@ee/100*60+@ee%100)-(@ss/100*60+@ss%100)    
end

else
begin
    if @end_time<=@ee
    begin
        if @end_time>=@ss
            select @period=(@end_time/100*60+@end_time%100)-(@start_time/100*60+@start_time%100)
        else
            select @period=0            
    end
    else
        select @period=(@ee/100*60+@ee%100)-(@start_time/100*60+@start_time%100)
end    

return @period
end
go

--调用自定义函数
select * 
    ,replace(substring(convert(char,start_time,120),charindex(':',start_time)-2,5),':','') as st
    ,replace(substring(convert(char,end_time,120),charindex(':',end_time)-2,5),':','') as en
    ,dbo.fgetperiod(start_time,end_time,ss,ee) as period
from temp 

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,