Objective

Write a SQL query to identify all project assignments where an employee is allocated to multiple projects with overlapping timeframes. The result should include the project ID, employee ID, start date, and end date of each such assignment. Ensure that the output is ordered by project ID and employee ID.

Additional information

• You are provided with a table named assignments with the following columns:

• An employee has overlapping assignments if they are assigned to different projects where the date ranges intersect. Specifically, two assignments overlap if:

  • The projects are different (project_id differs).
  • The start date of one assignment is on or before the end date of the other assignment.
  • The end date of one assignment is on or after the start date of the other assignment.

• The query should return distinct records without duplicates.

• Order the final result set first by project_id in ascending order and then by employee_id in ascending order.

• Assume that all dates are valid and start_date is always on or before end_date for each assignment.

SELECT DISTINCT
    a1.project_id,
    a1.employee_id,
    a1.start_date,
    a1.end_date
FROM
    assignments a1
    JOIN assignments a2
        ON a1.project_id != a2.project_id
        AND a1.employee_id = a2.employee_id
WHERE
    a1.start_date <= a2.end_date
    AND a1.end_date >= a2.start_date
ORDER BY
    a1.project_id,
    a1.employee_id

Explanation

The objective is to find all cases where the same employee is assigned to two different projects with overlapping time periods.

Step 1: Self-JOIN - compare each assignment against every other

Join assignments to itself. The join conditions ensure we compare only different projects for the same employee.

FROM assignments a1
JOIN assignments a2
    ON a1.project_id != a2.project_id
    AND a1.employee_id = a2.employee_id

Step 2: WHERE - detect overlapping date ranges

Two date ranges overlap when each one's start is on or before the other's end. Both conditions must hold simultaneously.

WHERE a1.start_date <= a2.end_date
  AND a1.end_date >= a2.start_date

Step 3: DISTINCT - eliminate mirror duplicates

The self-join produces each pair twice (a1↔a2 and a2↔a1); SELECT DISTINCT removes the duplicates.

SELECT DISTINCT a1.project_id, a1.employee_id, a1.start_date, a1.end_date

Step 4: ORDER BY - organize results

ORDER BY a1.project_id, a1.employee_id