I have a table that goes like this (pec_za_starenje_prva_start_stop):
| ID | TimeLogged | Tag1 |
|---|---|---|
| 1 | 2022-10-25 21:56:19 | 1 |
| 2 | 2022-10-26 04:13:24 | 0 |
| 3 | 2022-10-26 05:42:54 | 1 |
| 4 | 2022-10-26 12:18:27 | 0 |
| 5 | 2022-10-26 14:29:36 | 1 |
| 6 | 2022-10-26 21:22:21 | 0 |
id | int
TimeLogged | DateTime
Tag1 | int
Number 1 from "Tag1" should signal that the cycle has started, and 0 should signal that the cycle has finished. So, I would need the table that would look like this:
| ID | start_cycle | end_cycle | total_time |
|---|---|---|---|
| 1 | 2022-10-25 21:56:19 | 2022-10-26 04:13:24 | 377minutes |
| 2 | 2022-10-26 05:42:54 | 2022-10-26 12:18:27 | 395minutes |
| 3 | 2022-10-26 14:29:36 | 2022-10-26 21:22:21 | 1132minutes |
It doesn't matter in which format the total time will be, this is just an example.
This is the closest thing that I found but it's not working properly, as it shows only the start time:
SELECT ID,
TimeLogged start_cycle,
next_TimeLogged end_cycle,
TIMEDIFF(next_TimeLogged, TimeLogged) total_time
FROM (
SELECT *, LEAD(TimeLogged) OVER (PARTITION BY ID ORDER BY TimeLogged)
next_TimeLogged
FROM pec_za_starenje_prva_start_stop
) t
WHERE Tag1 = '1'
ORDER BY start_cycle;
CodePudding user response：
WITH
cte AS (
SELECT TimeLogged TimeLoggedIn,
LEAD(TimeLogged) OVER win TimeLoggedOut,
Tag1,
LEAD(Tag1) OVER win tag2
FROM pec_za_starenje_prva_start_stop
WINDOW win AS (ORDER BY TimeLogged)
)
SELECT ROW_NUMBER() OVER (ORDER BY TimeLoggedIn) ID,
TimeLoggedIn start_cycle,
TimeLoggedOut end_cycle,
TIMESTAMPDIFF(MINUTE, TimeLoggedIn, TimeLoggedOut) total_time
FROM cte
WHERE Tag1 AND !Tag2;