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 |