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

Question

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.

`that had previous job as student and were then converted to "Employee"` -v- `if the student changes its job to employee then it should not be picked` which statement is the correct one? (they seem to be complete opposites) — Paul Maxwell, Jun 03 '22 at 03:18

score 2 · Accepted Answer · answered Jun 03 '22 at 03:41

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

Paul Maxwell · Answer 2 · 2022-06-03T05:10:53.747

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

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

2 Answers2