Find Employees Who Have Worked in More Than One Department Query Using MS SQL Server?

To find employees who have worked in more than one department using Microsoft SQL Server, you'll need to:

1. Identify employees with multiple department assignments.
2. Aggregate and filter these results to find employees who have been associated with more than one department.

Assuming you have the following tables:

• EmployeeDepartments table with columns: EmployeeID, DepartmentID, StartDate, EndDate (if applicable).

Here's how you can write a query to find employees who have worked in more than one department:

SQL Query

-- Find employees who have worked in more than one department

WITH DepartmentAssignments AS (

    SELECT

        EmployeeID,

        COUNT(DISTINCT DepartmentID) AS DepartmentCount

    FROM

        EmployeeDepartments

    GROUP BY

        EmployeeID

)

SELECT

    ea.EmployeeID,

    e.EmployeeName -- Assuming you have an Employees table with EmployeeName

FROM

    DepartmentAssignments ea

    JOIN Employees e ON ea.EmployeeID = e.EmployeeID

WHERE

    ea.DepartmentCount > 1

ORDER BY

    e.EmployeeID;

Explanation

1. DepartmentAssignments CTE:

   • COUNT(DISTINCT DepartmentID) AS DepartmentCount: Counts the number of distinct departments each employee has worked in.
   • GROUP BY EmployeeID: Groups the results by EmployeeID to aggregate the number of departments for each employee.

2. Main Query:

   • JOIN Employees e ON ea.EmployeeID = e.EmployeeID: Joins with the Employees table to get employee details (like EmployeeName).
   • WHERE ea.DepartmentCount > 1: Filters to get only those employees who have worked in more than one department.
   • ORDER BY e.EmployeeID: Orders the results by EmployeeID for better readability.

Additional Considerations

• Data Accuracy: Ensure that the EmployeeDepartments table accurately reflects the departments each employee has worked in.
• Handling Dates: If the EndDate is used, consider scenarios where employees might have overlapping dates or multiple assignments within the same department.
• Indexes: Indexes on EmployeeID and DepartmentID can help improve query performance.

Adjust column names and table structures based on your actual schema. This query will effectively identify employees who have worked in more than one department.