Managment Studio Query

Question

Wasn't sure what title to give this. I'm currently writing a query using the table below in SQL Server Management Studio 2012.

If an employee has Qualification 'BDE' then that supersedes Qualification 'RVT' and 'RVT' doesn't need to show in my output for EmpID 1. It does need to show for EMP2 because they don't have Qualification 'BDE'

I've tried various ways using Union and a row partition, I'm sure this isn't a particularly tough request but I'm running out of ideas

EmpID  Department  ObtainedDate  ExpiryDate   Qualification  DaystoExpire
 1        HR        2019-06-12   2024-06-12        BDE            1819     
 1        HR        2017-06-09   2021-09-18        FGA             821
 1        HR        2019-06-18   2021-09-18        RVT             821
 1        HR        2019-01-28   2020-01-28        HIJ             222
 1        HR        2019-06-03   2019-07-03        TTT              13
 2       payroll    2018-10-18   2019-10-18        RVT             120

What I would like

EmpID  Department  ObtainedDate  ExpiryDate   Qualification  DaystoExpire
 1        HR        2019-06-12   2024-06-12        BDE            1819     
 1        HR        2017-06-09   2021-09-18        FGA             821
 1        HR        2019-01-28   2020-01-28        HIJ             222
 1        HR        2019-06-03   2019-07-03        TTT              13
 2       payroll    2018-10-18   2019-10-18        RVT             120

If you create a table that assigns each qualification a QualificationClass and a priority within the class, you can join it with your table, then this boils down to [getting top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group). — Nickolay, Jun 20 '19 at 13:47

Red Devil · Accepted Answer · 2019-06-20T12:33:47.323

I think this is what you want, I have not taken all the columns as you have mentioned in your example.

As per your given data, this should work:

    Declare @t table (EMPID int ,Department varchar(50),obtaineddate varchar(50),ExpiryDate varchar(50),Qualification varchar(50),DaystoExpire INT)

insert into @t values (1,'HR','2019-06-12','2024-06-12','BDE',1819),
(1,'HR','2017-06-09','2021-09-18','FGA',821),
(1,'HR','2019-06-18','2021-09-18','RVT',821),
(1,'HR','2019-01-28','2020-01-28','HIJ',222),
(1,'HR','2019-06-03','2019-07-03','TTT',13),
(2,'Payroll','2018-10-18','2019-10-18','RVT',120)

select * from @t a 
where not exists ( select * from @t  where a.EMPID=EMPID and a.Qualification='RVT')

UNION 

select * from @t a 
where  exists ( select * from @t  where a.EMPID=EMPID and Qualification='RVT')
and not exists (select * from @t  where a.EMPID=EMPID and Qualification='BDE')
order by EMPID,Qualification

Thanks, this doesn't quite work as Emp1 is still showing as having qualification 'RVT'. Think it's to do with the second 'select' after the UNION. — Clem_Fandango, Jun 20 '19 at 12:21
Thanks for your help @Red Devil I can see what you've created does work, I'm just struggling to implement it into my query. Not sure what I'm doing wrong but will accept your answer as correct — Clem_Fandango, Jun 20 '19 at 12:54

Managment Studio Query

1 Answers1