By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (
person_id INT,
date_start TIMESTAMP,
actual_termination_date TIMESTAMP
);
INSERT INTO table_name
SELECT 1, TIMESTAMP '1970-01-01 00:00:00 UTC', TIMESTAMP '1971-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP '1972-01-01 00:00:00 UTC', TIMESTAMP '1973-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP '1974-01-01 00:00:00 UTC', TIMESTAMP '1975-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 1, TIMESTAMP '1976-01-01 00:00:00 UTC', TIMESTAMP '1977-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 2, TIMESTAMP '1970-01-01 00:00:00 UTC', TIMESTAMP '1971-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 2, TIMESTAMP '1972-01-01 00:00:00 UTC', TIMESTAMP '1973-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 2, TIMESTAMP '1974-01-01 00:00:00 UTC', TIMESTAMP '1975-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 3, TIMESTAMP '1970-01-01 00:00:00 UTC', TIMESTAMP '1971-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 3, TIMESTAMP '1972-01-01 00:00:00 UTC', TIMESTAMP '1973-01-01 00:00:00 UTC' FROM DUAL UNION ALL
SELECT 4, TIMESTAMP '1970-01-01 00:00:00 UTC', TIMESTAMP '1971-01-01 00:00:00 UTC' FROM DUAL;
10 rows affected
SELECT *
FROM table_name
MATCH_RECOGNIZE (
PARTITION BY person_id
ORDER BY date_start
MEASURES
first_rehire.date_start AS rehire1_start,
first_rehire.actual_termination_date AS rehire1_end,
second_rehire.date_start AS rehire2_start,
second_rehire.actual_termination_date AS rehire2_end
PATTERN (^ first_hire first_rehire? second_rehire?)
DEFINE first_hire AS 1 = 1
)
| PERSON_ID | REHIRE1_START | REHIRE1_END | REHIRE2_START | REHIRE2_END |
|---|---|---|---|---|
| 1 | 01-JAN-72 00.00.00.000000 | 01-JAN-73 00.00.00.000000 | 01-JAN-74 00.00.00.000000 | 01-JAN-75 00.00.00.000000 |
| 2 | 01-JAN-72 00.00.00.000000 | 01-JAN-73 00.00.00.000000 | 01-JAN-74 00.00.00.000000 | 01-JAN-75 00.00.00.000000 |
| 3 | 01-JAN-72 00.00.00.000000 | 01-JAN-73 00.00.00.000000 | null | null |
| 4 | null | null | null | null |
SELECT person_id,
rehire1_start,
rehire1_end,
rehire2_start,
rehire2_end
FROM (
SELECT person_id,
ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY date_start) AS rn,
LEAD(date_start, 1)
OVER (PARTITION BY person_id ORDER BY date_start) AS rehire1_start,
LEAD(actual_termination_date, 1)
OVER (PARTITION BY person_id ORDER BY date_start) AS rehire1_end,
LEAD(date_start, 2)
OVER (PARTITION BY person_id ORDER BY date_start) AS rehire2_start,
LEAD(actual_termination_date, 2)
OVER (PARTITION BY person_id ORDER BY date_start) AS rehire2_end
FROM table_name
) t
WHERE rn = 1;
| PERSON_ID | REHIRE1_START | REHIRE1_END | REHIRE2_START | REHIRE2_END |
|---|---|---|---|---|
| 1 | 01-JAN-72 00.00.00.000000 | 01-JAN-73 00.00.00.000000 | 01-JAN-74 00.00.00.000000 | 01-JAN-75 00.00.00.000000 |
| 2 | 01-JAN-72 00.00.00.000000 | 01-JAN-73 00.00.00.000000 | 01-JAN-74 00.00.00.000000 | 01-JAN-75 00.00.00.000000 |
| 3 | 01-JAN-72 00.00.00.000000 | 01-JAN-73 00.00.00.000000 | null | null |
| 4 | null | null | null | null |