关于时间(续2)

tim_spac 2008-04-16 08:41:57

/** 假设数据存储于表 cdr, 每条记录包括 bgnTime为该记录的开始时间， endTime为该记录的结束时间*/

declare @bgnTime datetime, @endTime datetime



set @bgnTime = '2008-4-15 10:00:00.000'

set @endTime = '2008-4-15 10:59:59.997'





-- 指定时段内的记录数 -----------------------------------------------

-- 按开始时间计算

select count(1) from cdr where bgnTime>=@bgnTime and bgnTime<@endTime

-- 按结束时间计算

select count(1) from cdr where endTime>@bgnTime and endTime<=@endTime

-- 按时间相交计算

select count(1) from cdr where endTime>@bgnTime and bgnTime<@endTime





-- 指定时段内的累计时长

select datediff(second

		,case when bgnTime>@bgnTime then bgnTime else @bgnTime end

		,case when endTime<@endTime then endTime else @endTime end

		)

from cdr where endTime>@bgnTime and bgnTime<@endTime





-- 指定时段内的最大并发 -----------------------------------------------

-- 各个时间点的并发数

select top 1 * from (

	select timepoint, cnt = (select count(1) from cdr b where bgnTime<=timepoint and endTime>timepoint)

	from (select distinct timepoint=bgnTime from cdr where bgnTime<@endTime and endTime>@bgnTime) as a 

) as a

order by cnt desc





-- 时间连续性分组 -----------------------------------------------

declare @tbgn table (id int identity(1,1), bgnTime datetime)

-- 该表每一数据的前一刻没有任何使用记录

insert into @tbgn 

select distinct bgnTime 

from cdr a 

where not exists (

	select 1 from cdr b where b.bgnTime<a.bgnTime and b.endTime>=a.bgnTime) 

order by bgnTime



declare @tend table (id int identity(1,1), endTime datetime)

-- 该表每一数据的后一刻没有任何使用记录

insert into @tend 

select distinct endTime 

from cdr a 

where not exists (

	select 1 from cdr b where b.bgnTime<=a.endTime and b.endTime>a.endTime) 

order by endTime



-- 分组整合

select a.id, bgnTime, endTime from @tbgn a, @tend b where a.id=b.id