TSQL Query возвращает значения за каждый час за последние 24 часа
У меня есть запрос, с которого я действительно не знаю, с чего начать. Я надеюсь, что кто-то может помочь мне с этим. Я начну с объяснения таблицы
У меня есть таблица устройств с четырьмя столбцами: Device_Id, Device_Status, Begin_dt, End_dt Есть 6 различных статусов, где 3 (для простоты, скажем, статус 1, 4 и 5) означают, что устройство находится «в сети».
Примером этой таблицы может быть
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
Мой запрос должен вернуть сумму ВРЕМЕНИ, все устройства имеют статус, который указывает «онлайн». за каждый час в течение 24 часов.
так что мое возвращение должно выглядеть
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 |
Таким образом, для каждого часа дня у меня может быть больше 1 часа онлайн-времени (очевидно), потому что, например, если у меня есть 5 устройств, подключенных к сети в течение всего часа, у меня будет 5 часов онлайн-времени на этот час.
Это довольно сложно, и я надеюсь, что я проделал хорошую работу, объясняя это, каждый, кто может помочь или дать предложение, будет очень признателен.
-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
Это то, что у меня есть сейчас, я получаю сообщение об ошибке на "а";
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,