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:

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:

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:

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:

but you need to include everything between the same date and 2 days out:

The added condition on employee_id prevents rows from joining to themselves.

Replace the join condition on department with the above condition:

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement