TSQL-Abfrage gibt Werte für jede Stunde der letzten 24 Stunden zurück

Ich habe eine Frage, von der ich nicht wirklich weiß, wie ich anfangen soll. Ich hoffe, jemand kann mir dabei helfen. Ich werde mit der Erläuterung der Tabelle beginnen

Ich habe eine Gerätetabelle mit vier Spalten: Device_Id, Device_Status, Begin_dt, End_dt. Es gibt 6 verschiedene Status, wobei 3 (der Einfachheit halber sagen wir Status 1, 4 und 5) bedeutet, dass das Gerät online ist.

Ein Beispiel für diese Tabelle könnte sein

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

Meine Abfrage muss die Summe der ZEIT zurückgeben. Alle Geräte haben einen Status, der für jede Stunde in einem Zeitraum von 24 Stunden "online" anzeigt.

so sollte meine Rückkehr aussehen

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 |

So kann ich für jede Stunde des Tages (offensichtlich) mehr als 1 Stunde Online-Zeit haben, da ich beispielsweise für diese Stunde 5 Stunden Online-Zeit hätte, wenn alle 5 Geräte für die gesamte Stunde online wären.

Das ist ziemlich komplex und ich hoffe, dass ich es gut erklärt habe. Jeder, der helfen oder einen Vorschlag machen kann, wird sehr geschätzt.

-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

Dies ist, was ich gerade habe, ich erhalte einen Fehler auf der ')'

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,

Antworten auf die Frage(2)

Ihre Antwort auf die Frage