retrieving first rehire and terminated dates and second rehire and termination of employee

Question

I want to write a SQL Query to fetch 1st rehire, termination date and 2nd rehire, termination dates of employee if employee doesn't having those dates then it will display blank(null) in result. Having table name as per_periods and date start column is contains hire/rehire date and actual termination date contains terminations dates.In image 1st&2nd re means rehire dates and 1st&2nd term means termination dates

having table data In picture follows: [enter image description here][1]

the query must work for all records. can anyone guide me

if you need more info please comment in commentbox. [1]: https://i.stack.imgur.com/YSqKL.png

Answer 1

In Oracle, from version 12, you can use MATCH_RECOGNIZE:

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
)

Or, in both Oracle and MySQL, you can use:

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;

Which, for the sample data:

CREATE TABLE table_name (person_id, date_start, actual_termination_date) AS
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;

Both output:

PERSON_ID	REHIRE1_START	REHIRE1_END	REHIRE2_START	REHIRE2_END
1	01-JAN-72 00.00.00.000000000 UTC	01-JAN-73 00.00.00.000000000 UTC	01-JAN-74 00.00.00.000000000 UTC	01-JAN-75 00.00.00.000000000 UTC
2	01-JAN-72 00.00.00.000000000 UTC	01-JAN-73 00.00.00.000000000 UTC	01-JAN-74 00.00.00.000000000 UTC	01-JAN-75 00.00.00.000000000 UTC
3	01-JAN-72 00.00.00.000000000 UTC	01-JAN-73 00.00.00.000000000 UTC	null	null
4	null	null	null	null

db<>fiddle here