I have a table below for example:
ID NAME Company StartDate EndDate Status
--------------------------------------------------------------------------------------
50 Bob ABC 11-15-2016 02-05-2017 Former
50 Bob XYZ 05-06-2014 05-06-2015 Former
50 Bob EFG 06-10-2019 NULL Active
50 Bob LMN 07-05-2019 NULL Active
I am trying to list their 3 most recent jobs in descending order, so their "Active" job will be first, but if they have 2 "Active" jobs like the example above, I only want to pull the "Active" job with the most recent start date, OR if both of the "Active" jobs have the same start date, then just randomly choose one of them, and skip the other Active, then move on to the their most recent former jobs. The "Active" jobs will always have a NULL EndDate value.
So the result set I am looking for is like below:
ID NAME Company StartDate EndDate Status rn
----------------------------------------------------------------------------------------------
50 Bob LMN 07-05-2019 NULL Active 1
50 Bob ABC 11-15-2016 02-05-2017 Former 2
50 Bob XYZ 05-06-2014 05-06-2015 Former 3
I have tried the below code, but it isn't able to check for two Active job status and give them a row number properly. I'd like for only one of the Active jobs to be rn = 1 based on the conditions stated above, and rn = 2 and rn = 3 will be the last 2 "Former" jobs:
select *, row_number() over (partition by ID, NAME order by CASE WHEN Status = 'Active' AND
EndDate is NULL THEN Status END desc, EndDate desc
) rn
from employment
You can do this with window functions. Assuming that "Active" and "Former" are the only two possible status values, you could do:
select *
from (
select t.*,
row_number() over (partition by id, status order by startDate desc) rn
from mytable t
) t
where
(status = 'Active' and rn = 1)
or (status <> 'Active' and rn <= 2)
If there may be other statuses, then you would change the window function to:
row_number() over (
partition by id, case when status = 'Active' then 1 else 0 end
order by startDate desc
) rn
Finally, if you want to use startDate to order "Active" recorod and endDate for others:
row_number() over (
partition by id, case when status = 'Active' then 1 else 0 end
order by case when status = 'Active' then startDate else endDate end desc
) rn