CODEWITHSUNDEEP
sqlsql-serverdatabase
Junaid Shirwani
Junaid Shirwani

Reputation: 336

Find name of employees hired on different joining date

enter image description hereI wrote a query to find the employess hired on same date.

this is the query

select a.name,b.name,a.joining,b.joining from [SportsStore].[dbo].[Employees] a, 
  [SportsStore].[dbo].[Employees] b where a.joining = b.joining and a.name>b.name

Then a question popped into my mind. How do i find those employess only who were hired on different dates? I tried something like this

select a.name,b.name,a.joining,b.joining from [SportsStore].[dbo].[Employees] a, 
  [SportsStore].[dbo].[Employees] b where a.joining != b.joining and a.name>b.name

but then i realized this doesnt make sense . I thought about a sub query but it wont work either because we are selecting from two tables.

So i searched and could not find anything.

So the question is how do we "Find name of employees hired on different joining date?"

enter image description here

Upvotes: 1

Views: 3498

Answers (3)

Junaid Shirwani
Junaid Shirwani

Reputation: 336

after getting the answer , I have another way to get the same result . Here it is. I Hope its helpful to others and someone might explain which approach is better and in what scenario .

select name,joining from [SportsStore].[dbo].[Employees] where joining not in
(
                 select joining
                 from [SportsStore].[dbo].[Employees]
                 group by joining
                 having count(*)=1
)

Upvotes: 0

McNets
McNets

Reputation: 10807

JOIN the Employees table with a subquery that counts the joining dates.

where j.num = 1

returns employees hired on different dates

where j.num > 1

returns employees hired on same date

select e.id, e.name, e.joining
from [SportsStore].[dbo].[Employees] e
     inner join (select joining, count(*) num
                 from [SportsStore].[dbo].[Employees]
                 group by joining) j
     on j.joining = e.joining
where j.num = 1;

+----+------+---------------------+
| id | name |       joining       |
+----+------+---------------------+
|  1 |  abc | 01.01.2017 00:00:00 |
+----+------+---------------------+
|  2 |  def | 01.01.2017 00:00:00 |
+----+------+---------------------+
|  5 |  mno | 01.01.2017 00:00:00 |
+----+------+---------------------+

+----+------+---------------------+
| id | name |       joining       |
+----+------+---------------------+
|  3 |  ghi | 02.01.2017 00:00:00 |
+----+------+---------------------+
|  4 |  jkl | 03.01.2017 00:00:00 |
+----+------+---------------------+

Can check it here: http://rextester.com/OOO96554

Upvotes: 1

Stephan Lechner
Stephan Lechner

Reputation: 35154

If you just need the names (and not the list of different hiring dates), the following rather simple query should do the job:

select id, name
from employee
group by id, name
having count(distinct joining) > 1

Upvotes: 0

Related Questions