Sum time of consecutive rows with condition of filed

Question

In a table organised like this

EventTime	Device	State	UpTime
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

I need to count how many times I change the "UpTime" field from state 1 to state 0, and how long the state 0 lasts until the next change to 1 of the UpTime field

Example: If we consider the table above, we would have

Device	EventTimeDown	EventTimeUp	TotalPeriodSecond
Device1	2024-01-01 04:49:14.0967985	2024-01-01 04:49:45.7530473	31
Device1	2024-01-01 04:50:34.1280488	2024-01-01 05:22:42.2938957	1928
Device1	2024-01-01 05:23:07.3095144	2024-01-01 05:26:55.1395437	228
Device1	2024-01-01 05:27:20.1395443	2024-01-01 05:27:21.8895430	1

I tried to use a cross apply but I cannot isolate the date of the first event with UpTime = 0 by deleting subsequent records with UpTime = 0 because I do not need them.

Thanks Andrea

Answer 1

As previous comments, working example: SQL Fiddle

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