断续时间求总和

有这样一种情况，很多人使用一台服务器，有表记录：工号，时间，开始/结束。求这台服务器总共被使用了多少时间？（一人在用也是用，多人在用也是用）

原始表总是最糟的：

Create table TRecords(recordperson varchar(5),recordtime datetime,recordstatus varchar(5))
--truncate table TRecords
insert into TRecords
select '001','2012-07-16 12:00:00','start' union all
select '001','2012-07-16 12:10:00','end' union all
select '002','2012-07-16 12:20:00','start' union all
select '003','2012-07-16 12:30:00','start' union all
select '004','2012-07-16 12:40:00','start' union all
select '003','2012-07-16 12:50:00','end' union all
select '002','2012-07-16 13:00:00','end' union all
select '004','2012-07-16 13:10:00','end' union all
select '001','2012-07-16 13:20:00','start' 

在这种情况下：总时间是1-2的10min，3-8的50min，总共60min。（12:10-12:20服务器未使用。）

9的话仅有开始时间，不完整的数据是不期望的，但是若实时查询的话，也可能出现。

步骤一：有始有终

算出每个人的开始时间-结束时间。

with cte1 as
(
select rn=ROW_NUMBER() over (order by recordtime),
* from TRecords
)
select rn,recordperson,recordtime as st
,(select min(recordtime) from cte1 t2 where t2.recordstatus='end' and t1.recordperson=t2.recordperson and t2.rn>t1.rn) as en
from cte1 t1
where t1.recordstatus='start' 

有null的话，可以去掉，也可以赋个最大默认值，这看需求了。（以下算总时间，所以去掉。）

步骤二：去除子集

003的时间完全被002覆盖，将这种情况去掉。

with cte1 as
(
select rn=ROW_NUMBER() over (order by recordtime),
* from TRecords
)

,cte2 as
(
select rn,recordperson,recordtime as st
,(select min(recordtime) from cte1 t2 where t2.recordstatus='end' and t1.recordperson=t2.recordperson and t2.rn>t1.rn) as en
from cte1 t1
where t1.recordstatus='start' 
)

select rn=ROW_NUMBER() over (order by rn),recordperson,st,en from cte2
where en is not null and rn not in
(select t1.rn from cte2 t1 inner join cte2 t2 on t1.st>t2.st and t1.en<t2.en
where t1.en is not null)

步骤三：标记合并

00表示孤岛，01表示开始，10表示结束

with cte1 as
(
select rn=ROW_NUMBER() over (order by recordtime),
* from TRecords
)

,cte2 as
(
select rn,recordperson,recordtime as st
,(select min(recordtime) from cte1 t2 where t2.recordstatus='end' and t1.recordperson=t2.recordperson and t2.rn>t1.rn) as en
from cte1 t1
where t1.recordstatus='start' 
)
,cte3 as
(
select rn=ROW_NUMBER() over (order by rn),recordperson,st,en from cte2
where en is not null and rn not in
(select t1.rn from cte2 t1 inner join cte2 t2 on t1.st>t2.st and t1.en<t2.en
where t1.en is not null)
)
select t1.*,
case when t1.st<t2.en then 1 else 0 end as flagst,
case when t1.en>t3.st then 1 else 0 end as flagen
from cte3 t1 
left join cte3 t2 on t1.rn=t2.rn+1 
left join cte3 t3 on t1.rn=t3.rn-1

步骤四：数据合并

with cte1 as
(
select rn=ROW_NUMBER() over (order by recordtime),
* from TRecords
)

,cte2 as
(
select rn,recordperson,recordtime as st
,(select min(recordtime) from cte1 t2 where t2.recordstatus='end' and t1.recordperson=t2.recordperson and t2.rn>t1.rn) as en
from cte1 t1
where t1.recordstatus='start' 
)
,cte3 as
(
select rn=ROW_NUMBER() over (order by rn),recordperson,st,en from cte2
where en is not null and rn not in
(select t1.rn from cte2 t1 inner join cte2 t2 on t1.st>t2.st and t1.en<t2.en
where t1.en is not null)
)
,cte4 as
(
select t1.*,
case when t1.st<t2.en then 1 else 0 end as flagst,
case when t1.en>t3.st then 1 else 0 end as flagen
from cte3 t1 
left join cte3 t2 on t1.rn=t2.rn+1 
left join cte3 t3 on t1.rn=t3.rn-1 
)
select t1.st,(select min(t2.en) from cte4 t2 where t2.rn>t1.rn and t2.flagst=1 and t2.flagen=0) as en
from cte4 t1 where t1.flagst=0 and flagen=1
union all
select cte4.st,cte4.en from cte4 where cte4.flagst+cte4.flagen=0
order by st

最后，算出总时间即可。select SUM(datediff(minute,st,en))...

说实话，这方法比较烦......数据量大的话还是多建几张表算吧。