By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
CREATE TABLE Operations (
EventTime DateTime2,
DeviceName VARCHAR(512),
DeviceStatus INT,
Flag Bit
);
INSERT INTO Operations (EventTime, DeviceName, DeviceStatus, Flag) VALUES
('2024-11-29 18:04:11.2361142', 'DeviceB', '1000', '0'),
('2024-11-29 18:05:54.7418091', 'DeviceB', '1000', '0'),
('2024-11-29 18:08:20.8451237', 'DeviceB', '1000', '0'),
('2024-11-29 18:11:39.1074294', 'DeviceB', '1000', '0'),
('2024-11-29 18:16:40.1076772', 'DeviceB', '2000', '1'),
('2024-11-30 03:44:55.2751548', 'DeviceB', '1000', '1'),
('2024-11-29 18:08:49.8317093', 'DeviceA', '1000', '0'),
('2024-11-29 18:08:57.3539314', 'DeviceA', '1000', '0'),
('2024-11-29 18:09:05.1978502', 'DeviceA', '1000', '0'),
('2024-11-29 18:09:08.6673125', 'DeviceA', '1000', '0'),
('2024-11-29 18:09:16.1053403', 'DeviceA', '2000', '1'),
('2024-11-29 18:09:27.3567976', 'DeviceA', '2000', '0'),
('2024-11-29 18:09:57.3746556', 'DeviceA', '2000', '0'),
('2024-11-29 18:15:07.8193011', 'DeviceA', '3004', '1'),
('2024-11-30 04:44:02.7432369', 'DeviceA', '2000', '1'),
('2024-11-30 04:45:21.7130785', 'DeviceA', '2016', '1'),
('2024-11-30 04:46:36.8242106', 'DeviceA', '2000', '1'),
('2024-11-30 04:53:58.1847945', 'DeviceA', '1000', '1'),
('2024-11-30 04:56:05.6637439', 'DeviceA', '1000', '0'),
('2024-11-30 04:56:28.4035914', 'DeviceA', '1000', '0');
SELECT * FROM Operations
| EventTime | DeviceName | DeviceStatus | Flag |
|---|---|---|---|
| 2024-11-29 18:04:11.2361142 | DeviceB | 1000 | False |
| 2024-11-29 18:05:54.7418091 | DeviceB | 1000 | False |
| 2024-11-29 18:08:20.8451237 | DeviceB | 1000 | False |
| 2024-11-29 18:11:39.1074294 | DeviceB | 1000 | False |
| 2024-11-29 18:16:40.1076772 | DeviceB | 2000 | True |
| 2024-11-30 03:44:55.2751548 | DeviceB | 1000 | True |
| 2024-11-29 18:08:49.8317093 | DeviceA | 1000 | False |
| 2024-11-29 18:08:57.3539314 | DeviceA | 1000 | False |
| 2024-11-29 18:09:05.1978502 | DeviceA | 1000 | False |
| 2024-11-29 18:09:08.6673125 | DeviceA | 1000 | False |
| 2024-11-29 18:09:16.1053403 | DeviceA | 2000 | True |
| 2024-11-29 18:09:27.3567976 | DeviceA | 2000 | False |
| 2024-11-29 18:09:57.3746556 | DeviceA | 2000 | False |
| 2024-11-29 18:15:07.8193011 | DeviceA | 3004 | True |
| 2024-11-30 04:44:02.7432369 | DeviceA | 2000 | True |
| 2024-11-30 04:45:21.7130785 | DeviceA | 2016 | True |
| 2024-11-30 04:46:36.8242106 | DeviceA | 2000 | True |
| 2024-11-30 04:53:58.1847945 | DeviceA | 1000 | True |
| 2024-11-30 04:56:05.6637439 | DeviceA | 1000 | False |
| 2024-11-30 04:56:28.4035914 | DeviceA | 1000 | False |
--Strip all but date and hour from a datetime value
--from https://www.sqlservercentral.com/forums/topic/how-to-remove-minutes-and-seconds-from-datetime
DECLARE @testDate DATETIME = '2012-03-27 12:57:21.097'
SELECT DATEADD(HH,DATEPART(HH,@testDate),CAST(CAST(@testDate AS DATE) AS DATETIME)) AS StrippedDate
| StrippedDate |
|---|
| 2012-03-27 12:00:00.000 |
--Generate a calendar table with 1 record per hour
--Generate dates at 1 hour intervals for 48 hours starting with 2024-11-29
--Crossjoin with distinct devicename
SELECT DATEADD(HOUR, Hours.value -1 , '2024-11-29') dte, DeviceName
FROM GENERATE_SERIES(1,48) Hours
CROSS JOIN
(SELECT distinct DeviceName
FROM Operations) do
| dte | DeviceName |
|---|---|
| 2024-11-29 00:00:00.000 | DeviceA |
| 2024-11-29 01:00:00.000 | DeviceA |
| 2024-11-29 02:00:00.000 | DeviceA |
| 2024-11-29 03:00:00.000 | DeviceA |
| 2024-11-29 04:00:00.000 | DeviceA |
| 2024-11-29 05:00:00.000 | DeviceA |
| 2024-11-29 06:00:00.000 | DeviceA |
| 2024-11-29 07:00:00.000 | DeviceA |
| 2024-11-29 08:00:00.000 | DeviceA |
| 2024-11-29 09:00:00.000 | DeviceA |
| 2024-11-29 10:00:00.000 | DeviceA |
| 2024-11-29 11:00:00.000 | DeviceA |
| 2024-11-29 12:00:00.000 | DeviceA |
| 2024-11-29 13:00:00.000 | DeviceA |
| 2024-11-29 14:00:00.000 | DeviceA |
| 2024-11-29 15:00:00.000 | DeviceA |
| 2024-11-29 16:00:00.000 | DeviceA |
| 2024-11-29 17:00:00.000 | DeviceA |
| 2024-11-29 18:00:00.000 | DeviceA |
| 2024-11-29 19:00:00.000 | DeviceA |
| 2024-11-29 20:00:00.000 | DeviceA |
| 2024-11-29 21:00:00.000 | DeviceA |
| 2024-11-29 22:00:00.000 | DeviceA |
| 2024-11-29 23:00:00.000 | DeviceA |
| 2024-11-30 00:00:00.000 | DeviceA |
| 2024-11-30 01:00:00.000 | DeviceA |
| 2024-11-30 02:00:00.000 | DeviceA |
| 2024-11-30 03:00:00.000 | DeviceA |
| 2024-11-30 04:00:00.000 | DeviceA |
| 2024-11-30 05:00:00.000 | DeviceA |
| 2024-11-30 06:00:00.000 | DeviceA |
| 2024-11-30 07:00:00.000 | DeviceA |
| 2024-11-30 08:00:00.000 | DeviceA |
| 2024-11-30 09:00:00.000 | DeviceA |
| 2024-11-30 10:00:00.000 | DeviceA |
| 2024-11-30 11:00:00.000 | DeviceA |
| 2024-11-30 12:00:00.000 | DeviceA |
| 2024-11-30 13:00:00.000 | DeviceA |
| 2024-11-30 14:00:00.000 | DeviceA |
| 2024-11-30 15:00:00.000 | DeviceA |
| 2024-11-30 16:00:00.000 | DeviceA |
| 2024-11-30 17:00:00.000 | DeviceA |
| 2024-11-30 18:00:00.000 | DeviceA |
| 2024-11-30 19:00:00.000 | DeviceA |
| 2024-11-30 20:00:00.000 | DeviceA |
| 2024-11-30 21:00:00.000 | DeviceA |
| 2024-11-30 22:00:00.000 | DeviceA |
| 2024-11-30 23:00:00.000 | DeviceA |
| 2024-11-29 00:00:00.000 | DeviceB |
| 2024-11-29 01:00:00.000 | DeviceB |
| 2024-11-29 02:00:00.000 | DeviceB |
| 2024-11-29 03:00:00.000 | DeviceB |
| 2024-11-29 04:00:00.000 | DeviceB |
| 2024-11-29 05:00:00.000 | DeviceB |
| 2024-11-29 06:00:00.000 | DeviceB |
| 2024-11-29 07:00:00.000 | DeviceB |
| 2024-11-29 08:00:00.000 | DeviceB |
| 2024-11-29 09:00:00.000 | DeviceB |
| 2024-11-29 10:00:00.000 | DeviceB |
| 2024-11-29 11:00:00.000 | DeviceB |
| 2024-11-29 12:00:00.000 | DeviceB |
| 2024-11-29 13:00:00.000 | DeviceB |
| 2024-11-29 14:00:00.000 | DeviceB |
| 2024-11-29 15:00:00.000 | DeviceB |
| 2024-11-29 16:00:00.000 | DeviceB |
| 2024-11-29 17:00:00.000 | DeviceB |
| 2024-11-29 18:00:00.000 | DeviceB |
| 2024-11-29 19:00:00.000 | DeviceB |
| 2024-11-29 20:00:00.000 | DeviceB |
| 2024-11-29 21:00:00.000 | DeviceB |
| 2024-11-29 22:00:00.000 | DeviceB |
| 2024-11-29 23:00:00.000 | DeviceB |
| 2024-11-30 00:00:00.000 | DeviceB |
| 2024-11-30 01:00:00.000 | DeviceB |
| 2024-11-30 02:00:00.000 | DeviceB |
| 2024-11-30 03:00:00.000 | DeviceB |
| 2024-11-30 04:00:00.000 | DeviceB |
| 2024-11-30 05:00:00.000 | DeviceB |
| 2024-11-30 06:00:00.000 | DeviceB |
| 2024-11-30 07:00:00.000 | DeviceB |
| 2024-11-30 08:00:00.000 | DeviceB |
| 2024-11-30 09:00:00.000 | DeviceB |
| 2024-11-30 10:00:00.000 | DeviceB |
| 2024-11-30 11:00:00.000 | DeviceB |
| 2024-11-30 12:00:00.000 | DeviceB |
| 2024-11-30 13:00:00.000 | DeviceB |
| 2024-11-30 14:00:00.000 | DeviceB |
| 2024-11-30 15:00:00.000 | DeviceB |
| 2024-11-30 16:00:00.000 | DeviceB |
| 2024-11-30 17:00:00.000 | DeviceB |
| 2024-11-30 18:00:00.000 | DeviceB |
| 2024-11-30 19:00:00.000 | DeviceB |
| 2024-11-30 20:00:00.000 | DeviceB |
| 2024-11-30 21:00:00.000 | DeviceB |
| 2024-11-30 22:00:00.000 | DeviceB |
| 2024-11-30 23:00:00.000 | DeviceB |
--Use event time if it exists for a given hour,otherwise use generated exact hour
with d as (
--Generate dates at 1 hour intervals for 48 hours
SELECT DATEADD(HOUR, Hours.value -1 , '2024-11-29') dte, DeviceName
FROM GENERATE_SERIES(1,48) Hours
CROSS JOIN
(SELECT distinct DeviceName
FROM Operations) do
)
select isnull(o.EventTime,d.dte) as EventTime,
isnull(o.DeviceName, d.devicename) as devicename,
isnull(DeviceStatus,
(SELECT top 1 DeviceStatus FROM Operations WHERE eventtime < dte ORDER BY eventtime Desc)
) as DeviceStatus,
CASE WHEN o.EventTime IS NULL THEN 0
ELSE
CASE WHEN DeviceStatus = LAG(DeviceStatus) OVER (ORDER BY EventTime) THEN 0 ELSE 1 END
END AS Flag
from d
LEFT JOIN Operations o ON o.devicename=d.devicename AND d.dte=DATEADD(HH,DATEPART(HH,o.EventTime),CAST(CAST(o.EventTime AS DATE) AS DATETIME))
WHERE isnull(o.DeviceName, d.devicename) LIKE 'Device%'
AND CAST( isnull(o.EventTime,d.dte) AS DATE) in ('2024-11-29','2024-11-30')
ORDER BY devicename desc, isnull(o.EventTime,d.dte)
| EventTime | devicename | DeviceStatus | Flag |
|---|---|---|---|
| 2024-11-29 00:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 01:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 02:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 03:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 04:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 05:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 06:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 07:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 08:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 09:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 10:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 11:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 12:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 13:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 14:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 15:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 16:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 17:00:00.0000000 | DeviceB | null | 0 |
| 2024-11-29 18:04:11.2361142 | DeviceB | 1000 | 1 |
| 2024-11-29 18:05:54.7418091 | DeviceB | 1000 | 0 |
| 2024-11-29 18:08:20.8451237 | DeviceB | 1000 | 0 |
| 2024-11-29 18:11:39.1074294 | DeviceB | 1000 | 1 |
| 2024-11-29 18:16:40.1076772 | DeviceB | 2000 | 1 |
| 2024-11-29 19:00:00.0000000 | DeviceB | 2000 | 0 |
| 2024-11-29 20:00:00.0000000 | DeviceB | 2000 | 0 |
| 2024-11-29 21:00:00.0000000 | DeviceB | 2000 | 0 |
| 2024-11-29 22:00:00.0000000 | DeviceB | 2000 | 0 |
| 2024-11-29 23:00:00.0000000 | DeviceB | 2000 | 0 |
| 2024-11-30 00:00:00.0000000 | DeviceB | 2000 | 0 |
| 2024-11-30 01:00:00.0000000 | DeviceB | 2000 | 0 |
| 2024-11-30 02:00:00.0000000 | DeviceB | 2000 | 0 |
| 2024-11-30 03:44:55.2751548 | DeviceB | 1000 | 1 |
| 2024-11-30 04:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 05:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 06:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 07:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 08:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 09:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 10:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 11:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 12:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 13:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 14:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 15:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 16:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 17:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 18:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 19:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 20:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 21:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 22:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-30 23:00:00.0000000 | DeviceB | 1000 | 0 |
| 2024-11-29 00:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 01:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 02:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 03:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 04:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 05:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 06:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 07:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 08:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 09:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 10:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 11:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 12:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 13:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 14:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 15:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 16:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 17:00:00.0000000 | DeviceA | null | 0 |
| 2024-11-29 18:08:49.8317093 | DeviceA | 1000 | 0 |
| 2024-11-29 18:08:57.3539314 | DeviceA | 1000 | 0 |
| 2024-11-29 18:09:05.1978502 | DeviceA | 1000 | 0 |
| 2024-11-29 18:09:08.6673125 | DeviceA | 1000 | 0 |
| 2024-11-29 18:09:16.1053403 | DeviceA | 2000 | 1 |
| 2024-11-29 18:09:27.3567976 | DeviceA | 2000 | 0 |
| 2024-11-29 18:09:57.3746556 | DeviceA | 2000 | 0 |
| 2024-11-29 18:15:07.8193011 | DeviceA | 3004 | 1 |
| 2024-11-29 19:00:00.0000000 | DeviceA | 2000 | 0 |
| 2024-11-29 20:00:00.0000000 | DeviceA | 2000 | 0 |
| 2024-11-29 21:00:00.0000000 | DeviceA | 2000 | 0 |
| 2024-11-29 22:00:00.0000000 | DeviceA | 2000 | 0 |
| 2024-11-29 23:00:00.0000000 | DeviceA | 2000 | 0 |
| 2024-11-30 00:00:00.0000000 | DeviceA | 2000 | 0 |
| 2024-11-30 01:00:00.0000000 | DeviceA | 2000 | 0 |
| 2024-11-30 02:00:00.0000000 | DeviceA | 2000 | 0 |
| 2024-11-30 03:00:00.0000000 | DeviceA | 2000 | 0 |
| 2024-11-30 04:44:02.7432369 | DeviceA | 2000 | 1 |
| 2024-11-30 04:45:21.7130785 | DeviceA | 2016 | 1 |
| 2024-11-30 04:46:36.8242106 | DeviceA | 2000 | 1 |
| 2024-11-30 04:53:58.1847945 | DeviceA | 1000 | 1 |
| 2024-11-30 04:56:05.6637439 | DeviceA | 1000 | 0 |
| 2024-11-30 04:56:28.4035914 | DeviceA | 1000 | 0 |
| 2024-11-30 05:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 06:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 07:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 08:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 09:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 10:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 11:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 12:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 13:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 14:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 15:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 16:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 17:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 18:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 19:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 20:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 21:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 22:00:00.0000000 | DeviceA | 1000 | 0 |
| 2024-11-30 23:00:00.0000000 | DeviceA | 1000 | 0 |