Tengo una consulta con la que realmente no sé cómo empezar. Espero que alguien pueda ayudarme con eso. Empezaré explicando la tabla.

Tengo una tabla de dispositivos con cuatro columnas: Device_Id, Device_Status, Begin_dt, End_dt hay 6 estados diferentes donde 3 (por simplicidad, digamos estado 1, 4 y 5) significa que el dispositivo está "en línea"

Un ejemplo de esta tabla podría ser

Id | Status| Begin                   |  End
001|     1 | 2012-09-01 00:00:00.000 | 2012-09-01 01:00:00.000
001|     2 | 2012-09-01 01:00:00.000 | 2012-09-01 01:35:00.000
001|     1 | 2012-09-01 01:35:00.000 | 2012-09-01 02:05:00.000
003|     1 | 2012-09-01 05:00:00.000 | 2012-09-01 07:02:00.000
004|     1 | 2012-09-01 01:00:00.000 | 2012-09-01 01:35:00.000
003|     2 | 2012-09-01 07:02:00.000 | NULL

Mi consulta debe devolver la Suma de la HORA, todos los dispositivos tienen un estado que indica "en línea" para cada hora en un período de 24 horas.

así que mi regreso debería ser como

Hour| Online_Time
0   | 5:30:12.11
1   | 3:30:12.11
2   | 4:30:12.11
3   | 5:30:12.11
4   | 6:30:12.11
5   | 4:00:00.00
6   | 1:30:12.11
7   | 3:30:12.11
8   | 4:30:12.11
etc |

Entonces, por cada hora del día puedo tener más de 1 hora de tiempo en línea (obviamente) porque, por ejemplo, si tengo 5 dispositivos todos en línea durante toda la hora, tendría 5 horas de tiempo en línea durante esa hora.

Esto es algo complejo y espero que haya hecho un buen trabajo explicándolo, cualquier persona que pueda ayudar o dar una sugerencia es muy apreciada.

-J

with const as (
    select dateadd(hour, 1, cast(cast(getdate() -1 as date) as datetime)) as midnnight
    ),
allhours as (
    select 0 as hour, midnight as timestart, dateadd(hour, 1, timestart) 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 3 as hour, dateadd(hour, 3, midnight), dateadd(hour, 4, midnight) from const union all
    select 4 as hour, dateadd(hour, 4, midnight), dateadd(hour, 5, midnight) from const union all
    select 5 as hour, dateadd(hour, 5, midnight), dateadd(hour, 6, midnight) from const union all
    select 6 as hour, dateadd(hour, 6, midnight), dateadd(hour, 7, midnight) from const union all
    select 7 as hour, dateadd(hour, 7, midnight), dateadd(hour, 8, midnight) from const union all
    select 8 as hour, dateadd(hour, 8, midnight), dateadd(hour, 9, midnight) from const union all
    select 9 as hour, dateadd(hour, 9, midnight), dateadd(hour, 10, midnight) from const union all
    select 10 as hour, dateadd(hour, 10, midnight), dateadd(hour, 11, midnight) from const union all
    select 11 as hour, dateadd(hour, 11, midnight), dateadd(hour, 12, midnight) from const union all
    select 12 as hour, dateadd(hour, 12, midnight), dateadd(hour, 13, midnight) from const union all
    select 13 as hour, dateadd(hour, 13, midnight), dateadd(hour, 14, midnight) from const union all
    select 14 as hour, dateadd(hour, 14, midnight), dateadd(hour, 15, midnight) from const union all
    select 15 as hour, dateadd(hour, 15, midnight), dateadd(hour, 16, midnight) from const union all
    select 16 as hour, dateadd(hour, 16, midnight), dateadd(hour, 17, midnight) from const union all
    select 17 as hour, dateadd(hour, 17, midnight), dateadd(hour, 18, midnight) from const union all
    select 18 as hour, dateadd(hour, 18, midnight), dateadd(hour, 19, midnight) from const union all
    select 19 as hour, dateadd(hour, 19, midnight), dateadd(hour, 20, midnight) from const union all
    select 20 as hour, dateadd(hour, 20, midnight), dateadd(hour, 21, midnight) from const union all
    select 21 as hour, dateadd(hour, 21, midnight), dateadd(hour, 22, midnight) from const union all
    select 22 as hour, dateadd(hour, 22, midnight), dateadd(hour, 23, midnight) from const union all
    select 23 as hour, dateadd(hour, 23, midnight), dateadd(hour, 24, midnight) from const union all
   ) 
select ah.hour,
   sum(datediff(ms, (case when ah.timestart >= dt.Begin_Dt then timestart else dt.Begin_Dt end),
                    (case when ah.timeend <= dt.End_Dt then ah.timeend else dt.End_Dt end))) as totalms,
   cast(dateadd(ms, sum(datediff(ms, (case when ah.timestart >= dt.Begin_Dt then timestart else dt.Begin_Dt end),
                                 (case when ah.timeend <= dt.End_Dt then ah.timeend else dt.End_Dt end))),0) as time
                                 ) as totalTime
from allhours as ah left outer join
     dataTable as dt
     on ah.timestart< coalesce(dt.End_dt, getdate()) and
        ah.timeend >= dt.Begin_Dt
group by ah.hour
order by ah.hour

Esto es lo que tengo ahora mismo, estoy recibiendo un error en el ')'

select 23 as hour, dateadd(hour, 23, midnight), dateadd(hour, 24, midnight) from const union all
   )  <----- Incorrect syntax near ')'. Expecting SELECT, or '('.
select ah.hour,