SQL Query to find the previous job , location of a particular job type

I have the below table with data –

I want to create a query that displays the employees that had previous job as student and were then converted to “Employee” even if the final status is ACTIVE/INACTIVE

emp_number   emp_status         eff_start           eff_end             job             Location            
99           ACTIVE             01-JAN-2020         03-MAR-2020        Student          Toronto             
99           ACTIVE             04-MAR-2020         15-AUG-2020        Student          Vancouver           
99           ACTIVE             16-AUG-2020         22-AUG-2020        Contractor       Toronto             
99           ACTIVE             23-AUG-2020         28-SEP-2020        Employee         Toronto             
99           INACTIVE           29-SEP-2020         31-DEC-4712        ex- EMP          Toronto         

10          ACTIVE              03-FEB-2021         06-AUG-2021       Part-Student      India
10          ACTIVE              07-AUG-2021         28-MAY-2022       Part-Student      Toronto
10          ACTIVE              29-MAY-2022         31-DEC-4712       Employee          Toronto

12          ACTIVE              03-FEB-2021         06-AUG-2021       Student           India
12          ACTIVE              07-AUG-2021         28-MAY-2022       Student           Toronto
12          ACTIVE              29-MAY-2022         31-DEC-4712       Contractor        Toronto 

I want to create a query that displays the below employees –

emp_number     Previos_Location         Previous_job            Current_eff_start            
99              Vancouver                Student                 29-sep-2020    
10              Toronto                  Part-Student            29-MAY-2022

The above output, should show if previous job has anyting like “Student” mentioned. The previous_location , job sould have the latest location and job from the time when the job was “student/part-student”. and current_eff_start should have the latest effective date of the employee.

if the student changes its job to anything apart from “employee” like “contractor” then it should not be picked.

Answer

Try below, try to improve the code if you want:

SELECT E.emp_number, STD.Location AS Previos_Location, STD.job AS Previous_job, E.eff_start AS Current_eff_start
FROM 
    (
        SELECT T.*
        FROM
        (
            SELECT emp_number, eff_start, Location, job, RANK() OVER (PARTITION BY emp_number ORDER BY eff_end DESC) RNK
            FROM Employee
            WHERE job IN ('Employee', 'ex- EMP')
        ) T
        WHERE T.RNK = 1 
    ) E
    INNER JOIN 
    (
        SELECT T.*
        FROM
        (
            SELECT emp_number, Location, job, RANK() OVER (PARTITION BY emp_number ORDER BY eff_end DESC) RNK
            FROM Employee
            WHERE job IN ('Student', 'Part-Student')
        ) T
        WHERE T.RNK = 1 
            
    ) STD ON E.emp_number = STD.emp_number

Fiddle