新手有关问题：给定时间在给定时间段中的长度

/*要求：对于任意两个时间(可能同一天，也可能间隔一天)，计算它们在给定时间段[ss,ee]里的时间长度。。下面的自定义函数基本思路是将start_time和end_time截取小时分钟转为int形式，再与ss，ee做比较，分类讨论。现在的问题是：不能实现间隔一天的情况，如何更改呢？*/if object_id('temp') is not null    drop table tempgo--ss和ee列，以0到2400（间隔为100）依次表示00:00到24: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,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);goif object_id('dbo.fgetperiod') is not null    drop function dbo.fgetperiodgocreate function fgetperiod(@start_time_0 datetime,@end_time_0 datetime,@ss int,@ee int)returns bigint as begindeclare @period int,@start_time int,@end_time intselect @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<[email protected] begin    if @end_time<[email protected]    begin        if @end_time>[email protected]            select @period=(@[email protected]_time%100)-(@[email protected]%100)        else            select @period=0                end    else         select @period=(@[email protected]%100)-(@[email protected]%100)    endelsebegin    if @end_time<[email protected]    begin        if @end_time>[email protected]            select @period=(@[email protected]_time%100)-(@[email protected]_time%100)        else            select @period=0                end    else        select @period=(@[email protected]%100)-(@[email protected]_time%100)end    return @periodendgo--调用自定义函数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 periodfrom temp 

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