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
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