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.
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