[SQL] Sum time of consecutive rows with a condition of a field [closed]

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.

Continue reading...