By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE YourTable (
"SlaveID" INTEGER,
"Register" INTEGER,
"Value" INTEGER,
"Timestamp" time,
"ProcessTime" time
);
INSERT INTO YourTable
("SlaveID", "Register", "Value", "Timestamp", "ProcessTime")
VALUES
('3', '250', '0', '13:30:10', NULL),
('3', '250', '0', '13:30:20', NULL),
('3', '250', '1', '13:30:30', NULL),
('3', '250', '1', '13:30:40', NULL),
('3', '250', '1', '13:30:50', NULL),
('3', '250', '1', '13:31:00', NULL),
('3', '250', '0', '13:31:10', NULL),
('3', '250', '0', '13:31:20', NULL),
('3', '250', '0', '13:32:30', NULL),
('3', '250', '0', '13:32:40', NULL),
('3', '250', '1', '13:32:50', NULL);
11 rows affected
WITH cte1 AS (
SELECT *,
PrevValue = LAG(t.Value) OVER (PARTITION BY t.SlaveID, t.Register ORDER BY t.Timestamp)
FROM YourTable t
),
cte2 AS (
SELECT *,
NextTime = LEAD(t.Timestamp) OVER (PARTITION BY t.SlaveID, t.Register ORDER BY t.Timestamp)
FROM cte1 t
WHERE (t.Value <> t.PrevValue OR t.PrevValue IS NULL)
)
SELECT
t.SlaveID,
t.Register,
StartTime = t.Timestamp,
Endtime = t.NextTime
FROM cte2 t
WHERE t.Value = 1;
| SlaveID | Register | StartTime | Endtime |
|---|---|---|---|
| 3 | 250 | 13:30:30.0000000 | 13:31:10.0000000 |
| 3 | 250 | 13:32:50.0000000 | null |