FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

select 
    *
INTO #Events
FROM (
    VALUES
        ('2024-01-01 04:48:49.080', 'device_1', 1000, 1),
        ('2024-01-01 04:49:14.097', 'device_1', 1000, 0),
        ('2024-01-01 04:49:45.753', 'device_1', 1000, 1),
        ('2024-01-01 04:50:34.127', 'device_1', 1000, 0),
        ('2024-01-01 04:51:25.770', 'device_1', 1000, 0),
        ('2024-01-01 04:52:45.423', 'device_1', 2000, 0),
        ('2024-01-01 04:55:05.253', 'device_1', 3004, 0),
        ('2024-01-01 04:55:28.613', 'device_1', 2018, 0),
        ('2024-01-01 05:19:28.623', 'device_1', 3004, 0),
        ('2024-01-01 05:20:08.623', 'device_1', 2000, 0),
        ('2024-01-01 05:20:21.997', 'device_1', 2016, 0),
        ('2024-01-01 05:21:35.450', 'device_1', 2000, 0),
        ('2024-01-01 05:21:48.823', 'device_1', 1000, 0),
        ('2024-01-01 05:22:09.027', 'device_1', 1000, 0),
        ('2024-01-01 05:22:42.293', 'device_1', 3004, 1),
        ('2024-01-01 05:23:07.310', 'device_1', 3004, 0),
        ('2024-01-01 05:24:05.060', 'device_1', 2000, 0),
        ('2024-01-01 05:24:18.403', 'device_1', 2016, 0),
        ('2024-01-01 05:24:25.310', 'device_1', 2016, 0),
        ('2024-01-01 05:25:34.980', 'device_1', 2000, 0),
        ('2024-01-01 05:25:44.980', 'device_1', 1000, 0),
        ('2024-01-01 05:26:08.543', 'device_1', 1000, 0),
        ('2024-01-01 05:26:55.140', 'device_1', 1000, 1),
        ('2024-01-01 05:27:20.140', 'device_1', 1000, 0),
        ('2024-01-01 05:27:21.890', 'device_1', 1000, 1)
) q (EventTime, Device, State, UpTime)

SELECT
      Device,
      MIN(EventTime),
      MAX(NextEventTime)
    FROM
    (
        SELECT
            *,
            SUM(Uptime)     OVER (PARTITION BY Device ORDER BY EventTime) grp,
            LEAD(EventTime) OVER (PARTITION BY Device ORDER BY EventTime) NextEventTime
        FROM
          #Events
    )
      AS partitioned
    WHERE
      Uptime = 0
    GROUP BY
      Device, grp
    ORDER BY
      Device, grp
    ;
 
DROP TABLE #Events

Device		_2
device_1	2024-01-01 04:49:14.097	2024-01-01 04:49:45.753
device_1	2024-01-01 04:50:34.127	2024-01-01 05:22:42.293
device_1	2024-01-01 05:23:07.310	2024-01-01 05:26:55.140
device_1	2024-01-01 05:27:20.140	2024-01-01 05:27:21.890

(25 rows affected)

(4 rows affected)