您的问题似乎是时间跨度需要以小时为单位细分.因此，您需要从一天中的所有时间开始.然后计算重叠，总结差异(以毫秒为单位)并将所有内容转换回输出时间.

Your problem seems to be that the time span needs to be broken up in hours. So, you need to start with all hours in the day. Then you calculate the overlap, sum up the differences (below in milliseconds) and convert everything back to a time for the output.

with const as (
        select dateadd(hour, 1, cast(cast(getdate() -1 as date) as datetime)) as midnight            
    ),
    allhours as (
        select 0 as hour, midnight as timestart, dateadd(hour, 1, midnight) as timeend from const union all
        select 1 as hour, dateadd(hour, 1, midnight), dateadd(hour, 2, midnight) from const union all
        select 2 as hour, dateadd(hour, 2, midnight), dateadd(hour, 3, midnight)  from const union all
        . . .
        select 23 as hour, dateadd(hour, 23, midnight), dateadd(hour, 24, midnight) from const
    )
select ah.hour,
       sum(datediff(ms, (case when ah.timestart >= dt.begin then timestart else dt.begin end),
                        (case when ah.timeend <= dt.end then ah.timeend else dt.end end)
                   ) 
           ) as totalms,
       cast(dateadd(ms, sum(datediff(ms, (case when ah.timestart >= dt.begin then timestart else dt.begin end),
                                     (case when ah.timeend <= dt.end then ah.timeend else dt.end end)
                                    )
                           ),
                     0) as time
           ) as totalTime
from allhours ah left outer join
     DeviceTable dt
     on ah.timestart< coalesce(dt.end, getdate()) and
        ah.timeend >= dt.begin
group by ah.hour
order by ah.hour

此外，要使这项工作正常进行，您需要将begin"和end"括在双引号或方括号中.这些是 T-SQL 中的保留字.你需要替换......"3 到 22 小时有额外的线路.

Also, to make this work, you need to wrap "begin" and "end" in double quotes or square brackets. These are reserved words in T-SQL. And you need to replace the ". . ." with additional lines for hours from 3 to 22.