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.
CodePudding user response：
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
CodePudding user response：
You can use row_number() over() to determine the "most recent row" (by using descending order on a date/time column) then limit the output to just those rows by filtering on the row number result e.g.
select
*
from (
select
*
, row_number() over(partition by emp_number order by eff_end DESC) as rn
from mytable
where job like '%student%'
) d
where rn = 1
For the condition that someone has had both a student job and non-student job:
select
*
from (
select
mytable.*
, row_number() over(partition by emp_number
, case when lower(job) like '%student%' then 1 else 2 end
order by eff_end DESC) as rn
, count(case when lower(job) like '%student%' then 1 end) over(partition by emp_number) as sjobs
, count(case when lower(job) NOT like '%student%'
and lower(job) NOT like '%contract%'
then 1 end) over(partition by emp_number) as ojobs
from mytable
) d
where rn = 1
and lower(job) like '%student%'
and sjobs > 0
and ojobs > 0
------------ ------------ ----------- ----------- -------------- ----------- ---- ------- -------
| EMP_NUMBER | EMP_STATUS | EFF_START | EFF_END | JOB | LOCATION | RN | SJOBS | OJOBS |
------------ ------------ ----------- ----------- -------------- ----------- ---- ------- -------
| 10 | ACTIVE | 07-AUG-21 | 28-MAY-22 | Part-Student | Toronto | 1 | 2 | 1 |
| 99 | ACTIVE | 04-MAR-20 | 15-AUG-20 | Student | Vancouver | 1 | 2 | 3 |
------------ ------------ ----------- ----------- -------------- ----------- ---- ------- -------
or if you wish to ensure there were no non-student jobs change the where clause to:
and sjobs > 0
and ojobs = 0 /* no non-student job */
see: db<>fiddle here