Skip to content
Advertisement

SQL: How do I find records that are within X days of one another?

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'
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement