Eu tenho uma consulta que eu realmente não sei como começar. Espero que alguém possa me ajudar com isso. Vou começar explicando a tabela

Eu tenho uma tabela de dispositivos com quatro colunas: Device_Id, Device_Status, Begin_dt, End_dt, há 6 status diferentes em que 3 (para simplificar, digamos status 1, 4 e 5) significa que o dispositivo está 'online'

um exemplo desta tabela poderia 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

Minha consulta precisa retornar a Soma do TEMPO. Todos os dispositivos têm um status que indica "on-line" para cada hora em um período de 24 horas.

então meu retorno deve parecer

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 |

Então, para cada hora do dia eu posso ter mais de 1 hora de tempo on-line (obviamente) porque, por exemplo, se eu tiver 5 dispositivos todos on-line por toda a hora, eu teria 5 horas de tempo on-line para essa hora.

Isso é meio complexo e espero ter feito um bom trabalho explicando isso, qualquer um que possa ajudar ou dar uma sugestão é muito apreciado.

-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

Isto é o que eu tenho agora, estou recebendo um erro no ')'

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,