Prioritise temporary contract over permanent-CodePudding

I need to show everyone with their most current contract/s, I've used MAX on the contract start date to achieve this. Where someone has a temporary contract which is newer than their current permanent contract I would only like to show the temporary contract.

     ID     Name   Contract Start    Department        Contract Status
     ab     Dave      2020-06-01      Finance         Permanent Contract
     de     Sam       2022-06-01      Finance         Temporary Contract
     de     Sam       2021-04-13      HR              Permanent Contract
     jd     Jill      2019-03-01      I.T             Permanent Contract
     jd     Jill      2018-04-01      Supplies        Permanent Contract
     jd     Jill      2017-03-01      Admin           Permanent Contract
     pe     Pete      2019-04-01      Finance         Temporary Contract

What I would like to see

      ID     Name   Contract Start    Department        Contract Status
      ab     Dave      2020-06-01      Finance         Permanent Contract
      de     Sam       2022-06-01      Finance         Temporary Contract
      jd     Jill      2019-03-01      I.T             Permanent Contract
      jd     Jill      2018-04-01      Supplies        Permanent Contract
      jd     Jill      2017-03-01      Admin           Permanent Contract
      pe     Pete      2019-04-01      Finance         Temporary Contract

I thought a row number partition would solve my problem

 select
 Row_Number() over (partition by ID ORDER BY ContractStart DESC) as [Row_Number],
 ID,
 Name,
 MAX(Contract_Start),
 Department,
 Contract Status

 from various tables 

 group by Id,name,contract_start,department

 where Row_Number = 1

Row_Number     ID     Name   Contract Start    Department        Contract Status
   1           ab     Dave      2020-06-01      Finance         Permanent Contract
   1           de     Sam       2022-06-01      Finance         Temporary Contract
   1           jd     Jill      2017-03-01      I.T             Permanent Contract
   1           pe     Pete      2019-04-01      Finance         Temporary Contract

This works with Sam's contract but unfortunately this method loses Jill's 2 other permanent contracts.

CodePudding user response：

You can use not exists with a correlated query:

select * 
from t
where not exists (
  select * from t t2 
  where t2.id = t.id 
    and t.Contract_Status = 'Permanent Contract' 
    and t2.Contract_Status = 'Temporary Contract' 
    and t2.Contract_Start > t.Contract_Start
);

CodePudding user response：

I would break it into the union of two queries. First, get all the temporary contracts. Then union that with the permanent contracts excluding anyone with a temporary contract.

Example:

WITH temps AS (
select 
 ID,
 Name,
 MAX(Contract_Start),
 Department,
 Contract Status
from various_tables 
where [Contract Status] = 'Temporary Contract'
group by ID,Name,Department,[Contract Status]
),

perms AS (
select 
 ID,
 Name,
 MAX(Contract_Start),
 Department,
 Contract Status
from various_tables 
where [Contract Status] = 'Permanent Contract'
group by ID,Name,Department,[Contract Status]
)

SELECT * 
FROM temps

UNION

SELECT * 
FROM perms 
WHERE ID NOT IN (SELECT ID FROM temps)