SQL - Identify Hire and Rehire History with Termination Dates for a Specific Person

SELECT
    ppos.PERIOD_OF_SERVICE_ID,
    papf.PERSON_ID,
    papf.PERSON_NUMBER,
    paaf.ASSIGNMENT_NUMBER,

    CASE 
        WHEN COUNT(DISTINCT ppos.PERIOD_OF_SERVICE_ID) OVER (PARTITION BY papf.PERSON_ID) = 1 THEN 'Hire'
        WHEN ROW_NUMBER() OVER (PARTITION BY papf.PERSON_ID ORDER BY ppos.DATE_START ASC) = 1 THEN 'Hire'
        ELSE 'Rehire'
    END AS SERVICE_TYPE,

    TO_CHAR(
        MIN(ppos.DATE_START) OVER (PARTITION BY papf.PERSON_ID),
        'MM/DD/YYYY'
    ) AS HIRE_DATE,

    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY papf.PERSON_ID ORDER BY ppos.DATE_START ASC) = 1
        THEN TO_CHAR(ppos.ACTUAL_TERMINATION_DATE, 'MM/DD/YYYY')
        ELSE NULL
    END AS HIRE_TERMINATION_DATE,

    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY papf.PERSON_ID ORDER BY ppos.DATE_START ASC) = 2
        THEN TO_CHAR(ppos.DATE_START, 'MM/DD/YYYY')
        ELSE NULL
    END AS REHIRE_DATE,

    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY papf.PERSON_ID ORDER BY ppos.DATE_START ASC) = 2
        THEN TO_CHAR(ppos.ACTUAL_TERMINATION_DATE, 'MM/DD/YYYY')
        ELSE NULL
    END AS REHIRE_TERMINATION_DATE

FROM
    PER_PERIODS_OF_SERVICE ppos
    JOIN PER_ALL_PEOPLE_F papf
        ON ppos.PERSON_ID = papf.PERSON_ID
        AND SYSDATE BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
    JOIN PER_ALL_ASSIGNMENTS_F paaf
        ON paaf.PERSON_ID = papf.PERSON_ID
        AND SYSDATE BETWEEN paaf.EFFECTIVE_START_DATE AND paaf.EFFECTIVE_END_DATE
        AND paaf.ASSIGNMENT_TYPE = 'E'
WHERE
    papf.PERSON_NUMBER = :person_number
    AND ppos.ACTUAL_TERMINATION_DATE IS NOT NULL
ORDER BY
    ppos.DATE_START ASC

I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.