有这么几条设备运行记录
设备1 ： 开机2015-01-01 08:00:00 关机2015-01-01 12:00:00
设备1：开机2015-01-01 14:00:00 关机2015-01-01 19:00:00
设备1： 开机2015-01-02 08:00:00 关机2015-01-02 20:00:00
设备1：开机2015-01-03 08:00:00 关机2015-01-06 12:00:00
求设备1在2015-01-01日的运行时长？
求设备1在2015-01-02日的运行时长？
求设备1在2015-01-05日的运行时长？
上三个问题分别代表的是设备运行的不同种情况，可能一天开关多次，可能一天开关一次，可能连续运行几天，等等
怎么写一个函数方法把这些情况都分析清楚，准确的把设备在一天内的运行时长计算出来？？
小弟想了很久都没什么好办法，各路大神求救
------解决思路----------------------
with
cte1 as --存放每天的开始和结束时间
(
select convert(datetime, '2015-01-01 00:00:00') BTime, convert(datetime, '2015-01-02 00:00:00') ETime union all
select convert(datetime, '2015-01-02 00:00:00') BTime, convert(datetime, '2015-01-03 00:00:00') ETime union all
select convert(datetime, '2015-01-03 00:00:00') BTime, convert(datetime, '2015-01-04 00:00:00') ETime union all
select convert(datetime, '2015-01-04 00:00:00') BTime, convert(datetime, '2015-01-05 00:00:00') ETime union all
select convert(datetime, '2015-01-05 00:00:00') BTime, convert(datetime, '2015-01-06 00:00:00') ETime union all
select convert(datetime, '2015-01-06 00:00:00') BTime, convert(datetime, '2015-01-07 00:00:00') ETime union all
select convert(datetime, '2015-01-07 00:00:00') BTime, convert(datetime, '2015-01-08 00:00:00') ETime union all
select convert(datetime, '2015-01-08 00:00:00') BTime, convert(datetime, '2015-01-09 00:00:00') ETime
),
cte2 as --存放设备开机和关机时间
(
select convert(datetime, '2015-01-01 08:00:00') BeginTime, convert(datetime, '2015-01-01 12:00:00') EndTime union all
select convert(datetime, '2015-01-01 14:00:00') BeginTime, convert(datetime, '2015-01-01 19:00:00') EndTime union all
select convert(datetime, '2015-01-02 08:00:00') BeginTime, convert(datetime, '2015-01-02 20:00:00') EndTime union all
select convert(datetime, '2015-01-03 08:00:00') BeginTime, convert(datetime, '2015-01-06 12:00:00') EndTime
)
select BTime
, sum(case
when b.BeginTime between a.BTime and a.ETime and b.EndTime between a.BTime and a.ETime then convert(float, b.EndTime-b.BeginTime) * 24
when b.BeginTime between a.BTime and a.ETime then convert(float, a.ETime-b.BeginTime) * 24
when b.EndTime between a.BTime and a.ETime then convert(float, b.EndTime-a.BTime) * 24
else 24
End) SumTime
from cte1 a, cte2 b
where a.ETime>b.BeginTime and a.BTime<b.EndTime
group by BTime