Suppose I have a database called employees
which looks like this:
employee_id department hire_date 1 Auto 2000-01-05 2 Auto 2000-01-06 3 Sports 2000-01-06 4 Auto 2000-01-10 5 Toys 2000-02-20 6 Tools 2000-02-22 7 Toys 2000-02-23
I want to return the employees that were hired within 2 days of one another.
Here’s the closest that I’ve been able to get:
SELECT e1.employee_id, e1.hire_date, e1.department, e2.employee_id, e2.hire_date, e2.department FROM employees AS e1 JOIN employees AS e2 ON e1.department = e2.department WHERE e2.hire_date = e1.hire_date + INTERVAL '2 days'
The code above gives me the employees that were hired within 2 days of on another but only for their respective departments. Meaning, that it will return employees 1 and 2 (Auto) instead of employees 1, 2 & 3 (Auto & Sports). I’m assuming this is because I did a self-join using the common department
column.
I can’t figure out how to return the list of employees that were hired within 2 days of one another, regardless of department.
Edit: The following code also does not work as it is only returning the hire dates less than 2 days in the same department, not across all departments:
SELECT e1.employee_id, e1.hire_date, e1.department, e2.employee_id, e2.hire_date, e2.department FROM employees AS e1 JOIN employees AS e2 ON e1.department = e2.department WHERE e2.hire_date BETWEEN e1.hire_date + INTERVAL '1 days' AND e1.hire_date + INTERVAL '2 days'
Edit 2: Both answers provided by @dzhukov and @Bohemian work. See my comments under their answers for the final result for my actual dataset.
Advertisement
Answer
If you don’t want to restrict the join to department, why do you have that as a join criteria?
Also, you are only returning rows that have exactly 2 days difference:
WHERE e2.hire_date = e1.hire_date + INTERVAL '2 days'
but you need to include everything between the same date and 2 days out:
WHERE e2.hire_date between e1.hire_date and e1.hire_date + INTERVAL '2 days' AND e2.employee_id != e1.employee_id
The added condition on employee_id prevents rows from joining to themselves.
Replace the join condition on department with the above condition:
SELECT ... FROM employees AS e1 JOIN employees AS e2 ON e2.employee_id != e1.employee_id AND e2.hire_date between e1.hire_date and e1.hire_date + INTERVAL '2 days'