对于给定登陆和退出时间，要怎么统计每个时间段的使用情况呢？

Question

已有的业务系统里只统计了登陆和退出2个时间，如果想利用这两个值来统计出每个时间段业务使用情况该怎么处理呢？比如：13：13：13 登陆系统， 14：46：12退出系统，这样在13-14这个时间段就用了47分，在14-15这个时间段就用了46分（忽略秒）

Answer 1

代码如下，这段是精确到秒的，如果要精确到分钟，那就改“13:13:13”为“13:13:00”，“14:46:12”为“14:46:00”。
select 
BeginTime,
case 
when '13:13:13' between BeginTime and EndTime then round(convert(float, CONVERT(datetime, EndTime) - CONVERT(datetime, '13:13:13') + 1.0000 / 60 / 60 / 24) * 24 * 60, 2)
when '14:46:12' between BeginTime and EndTime then round(convert(float, CONVERT(datetime, '14:46:12') - CONVERT(datetime, BeginTime)) * 24 * 60, 2)
End 
from 
(
select '00:00:00' BeginTime, '00:59:59' EndTime
union all
select '01:00:00' BeginTime, '01:59:59' EndTime
union all
select '02:00:00' BeginTime, '02:59:59' EndTime
union all
select '03:00:00' BeginTime, '03:59:59' EndTime
union all
select '04:00:00' BeginTime, '04:59:59' EndTime
union all
select '05:00:00' BeginTime, '05:59:59' EndTime
union all
select '06:00:00' BeginTime, '06:59:59' EndTime
union all
select '07:00:00' BeginTime, '07:59:59' EndTime
union all
select '08:00:00' BeginTime, '08:59:59' EndTime
union all
select '09:00:00' BeginTime, '09:59:59' EndTime
union all
select '10:00:00' BeginTime, '10:59:59' EndTime
union all
select '11:00:00' BeginTime, '11:59:59' EndTime
union all
select '12:00:00' BeginTime, '12:59:59' EndTime
union all
select '13:00:00' BeginTime, '13:59:59' EndTime
union all
select '14:00:00' BeginTime, '14:59:59' EndTime
union all
select '15:00:00' BeginTime, '15:59:59' EndTime
union all
select '16:00:00' BeginTime, '16:59:59' EndTime
union all
select '17:00:00' BeginTime, '17:59:59' EndTime
union all
select '18:00:00' BeginTime, '18:59:59' EndTime
union all
select '19:00:00' BeginTime, '19:59:59' EndTime
union all
select '20:00:00' BeginTime, '20:59:59' EndTime
union all
select '21:00:00' BeginTime, '21:59:59' EndTime
union all
select '22:00:00' BeginTime, '22:59:59' EndTime
union all
select '23:00:00' BeginTime, '23:59:59' EndTime
) aa
where BeginTime <'14:46:12' and EndTime>'13:13:13'

Answer 2

噢，代码改了一下，忘记判断整小时的情况了。
select 
BeginTime,
case 
when '03:13:13' between BeginTime and EndTime then round(convert(float, CONVERT(datetime, EndTime) - CONVERT(datetime, '03:13:13') + 1.0000 / 60 / 60 / 24) * 24 * 60, 2)
when '14:46:12' between BeginTime and EndTime then round(convert(float, CONVERT(datetime, '14:46:12') - CONVERT(datetime, BeginTime)) * 24 * 60, 2)
else 60
End 
from 
(
select '00:00:00' BeginTime, '00:59:59' EndTime
union all
select '01:00:00' BeginTime, '01:59:59' EndTime
union all
select '02:00:00' BeginTime, '02:59:59' EndTime
union all
select '03:00:00' BeginTime, '03:59:59' EndTime
union all
select '04:00:00' BeginTime, '04:59:59' EndTime
union all
select '05:00:00' BeginTime, '05:59:59' EndTime
union all
select '06:00:00' BeginTime, '06:59:59' EndTime
union all
select '07:00:00' BeginTime, '07:59:59' EndTime
union all
select '08:00:00' BeginTime, '08:59:59' EndTime
union all
select '09:00:00' BeginTime, '09:59:59' EndTime
union all
select '10:00:00' BeginTime, '10:59:59' EndTime
union all
select '11:00:00' BeginTime, '11:59:59' EndTime
union all
select '12:00:00' BeginTime, '12:59:59' EndTime
union all
select '13:00:00' BeginTime, '13:59:59' EndTime
union all
select '14:00:00' BeginTime, '14:59:59' EndTime
union all
select '15:00:00' BeginTime, '15:59:59' EndTime
union all
select '16:00:00' BeginTime, '16:59:59' EndTime
union all
select '17:00:00' BeginTime, '17:59:59' EndTime
union all
select '18:00:00' BeginTime, '18:59:59' EndTime
union all
select '19:00:00' BeginTime, '19:59:59' EndTime
union all
select '20:00:00' BeginTime, '20:59:59' EndTime
union all
select '21:00:00' BeginTime, '21:59:59' EndTime
union all
select '22:00:00' BeginTime, '22:59:59' EndTime
union all
select '23:00:00' BeginTime, '23:59:59' EndTime
) aa
where BeginTime <'14:46:12' and EndTime>'03:13:13'

Answer 3

;WITH CTE AS(
SELECT CONVERT(VARCHAR(13),登陆时间,120)+':00:00' Point
,*
FROM TB
)
,CTE2 AS(
SELECT CASE WHEN T1.登陆时间>DATEADD(HOUR,T2.number,T1.Point)THEN T1.登陆时间 ELSE DATEADD(HOUR,T2.number,T1.Point) END StartTime
,CASE WHEN T1.退出时间<DATEADD(HOUR,T2.number+1,T1.Point)THEN T1.退出时间 ELSE DATEADD(HOUR,T2.number+1,T1.Point) END EndTime
FROM CTE T1
JOIN master..spt_values T2 ON T2.type='P'AND T2.number<=DATEDIFF(HOUR,T1.登陆时间,T1.退出时间)
)
SELECT DATEPART(HOUR,StartTime)时间段
,DATEDIFF(MINUTE,StartTime,EndTime)业务使用情况
FROM CTE2登陆时间 退出时间需要是时间类型