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,