By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE BackupTO (`record_id` INTEGER);
CREATE TABLE Records (
`record_id` INTEGER,
`subject` VARCHAR(15),
`start_timestamp` VARCHAR(19),
`end_timestamp` VARCHAR(19),
`interval` FLOAT,
`log_type` INTEGER DEFAULT 1
);
INSERT INTO Records
(`record_id`, `subject`, `start_timestamp`, `end_timestamp`, `interval`)
VALUES
('2', 'Start Product 2', '2021-04-21T16:22:39', '2021-04-21T16:23:40', '0.97'),
('3', 'error 1', '2021-04-21T16:25:44', '2021-04-21T16:25:54', '10.0'),
('4', 'End Product 2', '2021-04-21T16:30:13', '2021-04-21T16:30:14', '0.97'),
('5', 'Start Product 1', '2021-04-21T16:35:13', '2021-04-21T16:35:13', '0.6'),
('6', 'End Product 1', '2021-04-21T16:36:13', '2021-04-21T16:36:13', '0.45');
SELECT r.*
FROM Records r LEFT JOIN BackupTO b
ON b.record_id = r.record_id
WHERE b.record_id IS NULL AND r.log_type = 1 AND r.subject LIKE '%start%'
| record_id | subject | start_timestamp | end_timestamp | interval | log_type |
|---|---|---|---|---|---|
| 2 | Start Product 2 | 2021-04-21T16:22:39 | 2021-04-21T16:23:40 | 0.97 | 1 |
| 5 | Start Product 1 | 2021-04-21T16:35:13 | 2021-04-21T16:35:13 | 0.6 | 1 |
WITH cte AS (
SELECT r.*
FROM Records r LEFT JOIN BackupTO b
ON b.record_id = r.record_id
WHERE b.record_id IS NULL AND r.log_type = 1 AND r.subject LIKE '%start%'
)
SELECT *
FROM (
SELECT r.*,
MIN(CASE WHEN r.subject LIKE '%end%' THEN r.record_id END) OVER () id_end
FROM Records r INNER JOIN cte c
ON r.record_id > c.record_id
WHERE c.record_id = 2
)
WHERE COALESCE(record_id <= id_end, 1)
| record_id | subject | start_timestamp | end_timestamp | interval | log_type | id_end |
|---|---|---|---|---|---|---|
| 3 | error 1 | 2021-04-21T16:25:44 | 2021-04-21T16:25:54 | 10 | 1 | 4 |
| 4 | End Product 2 | 2021-04-21T16:30:13 | 2021-04-21T16:30:14 | 0.97 | 1 | 4 |
WITH cte AS (
SELECT r.*
FROM Records r LEFT JOIN BackupTO b
ON b.record_id = r.record_id
WHERE b.record_id IS NULL AND r.log_type = 1 AND r.subject LIKE '%start%'
)
SELECT *
FROM (
SELECT r.*,
MIN(CASE WHEN r.subject LIKE '%end%' THEN r.record_id END) OVER () id_end
FROM Records r INNER JOIN cte c
ON r.record_id > c.record_id
WHERE c.record_id = 5
)
WHERE COALESCE(record_id <= id_end, 1)
| record_id | subject | start_timestamp | end_timestamp | interval | log_type | id_end |
|---|---|---|---|---|---|---|
| 6 | End Product 1 | 2021-04-21T16:36:13 | 2021-04-21T16:36:13 | 0.45 | 1 | 6 |
SELECT *
FROM (
SELECT r.*,
MIN(CASE WHEN r.subject LIKE '%end%' THEN r.record_id END) OVER () id_end
FROM Records r
WHERE r.record_id > 2
)
WHERE COALESCE(record_id <= id_end, 1)
| record_id | subject | start_timestamp | end_timestamp | interval | log_type | id_end |
|---|---|---|---|---|---|---|
| 3 | error 1 | 2021-04-21T16:25:44 | 2021-04-21T16:25:54 | 10 | 1 | 4 |
| 4 | End Product 2 | 2021-04-21T16:30:13 | 2021-04-21T16:30:14 | 0.97 | 1 | 4 |
SELECT *
FROM (
SELECT r.*,
MIN(CASE WHEN r.subject LIKE '%end%' THEN r.record_id END) OVER () id_end
FROM Records r
WHERE r.record_id > 5
)
WHERE COALESCE(record_id <= id_end, 1)
| record_id | subject | start_timestamp | end_timestamp | interval | log_type | id_end |
|---|---|---|---|---|---|---|
| 6 | End Product 1 | 2021-04-21T16:36:13 | 2021-04-21T16:36:13 | 0.45 | 1 | 6 |