Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Employee (
  emp_number INT,
  emp_status VARCHAR(8),
  eff_start DATE,
  eff_end DATE,
  job VARCHAR(12),
  location VARCHAR(9)
);
INSERT INTO Employee
  (emp_number, emp_status, eff_start, eff_end, job, Location)
  SELECT '99', 'ACTIVE', '04-MAR-2020', '15-AUG-2020', 'Student', 'Vancouver' FROM DUAL UNION ALL
  SELECT '99', 'INACTIVE', '29-SEP-2020', '31-DEC-4712', 'ex- EMP', 'Toronto' FROM DUAL UNION ALL
  SELECT '10', 'ACTIVE', '03-FEB-2021', '06-AUG-2021', 'Part-Student', 'India' FROM DUAL UNION ALL
  SELECT '10', 'ACTIVE', '07-AUG-2021', '28-MAY-2022', 'Part-Student', 'Toronto' FROM DUAL UNION ALL
  SELECT '10', 'ACTIVE', '29-MAY-2022', '31-DEC-4712', 'Employee', 'Toronto' FROM DUAL UNION ALL
  SELECT '12', 'ACTIVE', '03-FEB-2021', '06-AUG-2021', 'Student', 'India' FROM DUAL UNION ALL
  SELECT '12', 'ACTIVE', '07-AUG-2021', '28-MAY-2022', 'Student', 'Toronto' FROM DUAL UNION ALL
  SELECT '12', 'ACTIVE', '29-MAY-2022', '31-DEC-4712', 'Contractor', 'Toronto' FROM DUAL UNION ALL
  SELECT '99', 'ACTIVE', '23-AUG-2020', '28-SEP-2020', 'Employee', 'Toronto' FROM DUAL UNION ALL
  SELECT '99', 'ACTIVE', '16-AUG-2020', '22-AUG-2020', 'Contractor', 'Toronto' FROM DUAL UNION ALL
  SELECT '99', 'ACTIVE', '01-JAN-2020', '03-MAR-2020', 'Student', 'Toronto'FROM DUAL
11 rows affected

        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 


EMP_NUMBER
LOCATION
JOB
RNK


10
Toronto
Part-Student
1

12
Toronto
Student
1

99
Vancouver
Student
1


        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 ('Employee', 'ex- EMP')
        ) T
        WHERE T.RNK = 1 


EMP_NUMBER
LOCATION
JOB
RNK


10
Toronto
Employee
1

99
Toronto
ex- EMP
1


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


EMP_NUMBER
PREVIOS_LOCATION
PREVIOUS_JOB
CURRENT_EFF_START


10
Toronto
Part-Student
29-MAY-22

99
Vancouver
Student
29-SEP-20