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, EventTimeDown, EventTime EventTimeUp, DATEDIFF(SECOND, EventTimeDown, EventTime) TotalPeriodSecond FROM (
    SELECT *, LAG(EventTime) OVER(PARTITION BY Device ORDER BY EventTime) EventTimeDown
    FROM (
        select
            *, LAG(UpTime) OVER(PARTITION BY Device ORDER BY EventTime) PUpTime
        from #Events
    ) q WHERE UpTime = 1 OR PUpTime = 1
) q WHERE UpTime = 1 AND PUpTime IS NOT NULL

DROP TABLE #Events

Device	EventTimeDown	EventTimeUp	TotalPeriodSecond
device_1	2024-01-01 04:49:14.097	2024-01-01 04:49:45.753	31
device_1	2024-01-01 04:50:34.127	2024-01-01 05:22:42.293	1928
device_1	2024-01-01 05:23:07.310	2024-01-01 05:26:55.140	228
device_1	2024-01-01 05:27:20.140	2024-01-01 05:27:21.890	1

(25 rows affected)

(4 rows affected)