How to display Employees list who joined at least N number of months before their Manager in SQL Ser-CodePudding

Below is the table I have created and inserted values in it:

CREATE TABLE employees   
(
    employeeID int,   
    employeeName varchar(25),  
    managerID int,   
    hireDate date
) 
   
INSERT INTO employees VALUES (1, 'James'  , NULL, '2016-01-01'),   
                             (2, 'John'   , 1, '2015-02-01'),  
                             (3, 'Robert' , 1, '2015-05-01'),  
                             (4, 'Michael', 2, '2016-06-01'),  
                             (5, 'William', 2, '2015-01-01'),  
                             (6, 'Richard', 3, '2015-01-01'),  
                             (7, 'Charles', 3, '2015-03-01'),  
                             (8, 'Thomas' , 3, '2016-07-01') 
GO

SELECT * FROM employees

As you can see from the screenshot above, John, whose manager is James, started working 11 months before James. You can also see that Robert, who is also under James’s management, started working 8 months before James.

Now the question is:

Write a query that displays the employees who started working at least 6 months before their manager.

Since, I am dealing with a single table, I figured out that I need to use SELF JOIN.

And this is what I have tried:

SELECT E.employeeName
FROM employees E 
LEFT JOIN employees M ON E.managerID = M.employeeID
                      AND E.hireDate > M.hireDate
                      AND DATEDIFF(MONTH, E.hireDate, M.hireDate) >= 6

I got the following result:

As you can see from the screenshot above, It is displaying all employees including those who did not join at least 6 months before their manager.

What change do I need to make in my query to display employees who joined at least 6 months before their manager ?

CodePudding user response：

Use JOIN instead of LEFT JOIN and change

AND E.hireDate > M.hireDate

to:

AND M.hireDate > E.hireDate

Like this:

SELECT E.employeeName
FROM employees E 
JOIN employees M ON E.managerID = M.employeeID
AND M.hireDate > E.hireDate
AND DATEDIFF(MONTH, E.hireDate, M.hireDate) >= 6

CodePudding user response：

You have a few issues here.

Firstly, you want employees who started working before their manager, so that's E.hireDate < M.hireDate, you have it the wrong way round.

Second, you're outer joining on this criteria which only affects the rows that match on the right side of the join - this doesn't filter any rows, you need to specify where m.EmployeeId is not null

select e.employeeName
from employees e 
left join employees m on m.employeeID = e.managerID 
  and e.hireDate < m.hireDate
  and DateDiff(month, e.hireDate, m.hireDate) >= 6
where m.employeeID is not null;

which actually turns the join into an inner join, so you can just remove the left keyword

select e.employeeName
from employees e 
join employees m on m.employeeID = e.managerID 
  and e.hireDate < m.hireDate
  and DateDiff(month, e.hireDate, m.hireDate) >= 6;

Since you're not returning any data from the joined table, this lends itself to be better expressed with exists

select E.employeeName
from employees E 
where exists (
  select * from employees m
  where m.employeeID = e.managerID 
    and e.hireDate < e.hireDate
    and DateDiff(month, e.hireDate, m.hireDate) >= 6
);