Oracle 21c

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

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