Find All Employees Who Joined in the Same Year as Their Manager Using MS SQL Server Query?
To find all employees who joined the company in the same year as their manager, you will need to work with a table that includes employee details and their respective managers. Let’s assume you have the following tables:
Employees:EmployeeIDManagerIDJoinDate
Here’s how you can write an SQL query to find all employees who joined in the same year as their manager:
SQL Query
Explanation:
Self-Join:
- The
Employeestable is joined with itself to relate employees with their managers using the conditione.ManagerID = m.EmployeeID.
- The
Filtering by Join Year:
YEAR(e.JoinDate) = YEAR(m.JoinDate): This condition filters the results to include only those employees whose join year matches the join year of their manager.
Example Data and Results
Assume the Employees table has the following data:
| EmployeeID | ManagerID | JoinDate |
|---|---|---|
| 1 | 2 | 2022-05-15 |
| 2 | NULL | 2022-03-10 |
| 3 | 2 | 2023-06-22 |
| 4 | 3 | 2023-04-05 |
| 5 | 2 | 2021-11-30 |
For this dataset:
- Employee 1 joined in 2022, and their manager (Employee 2) also joined in 2022.
- Employee 3 joined in 2023, and their manager (Employee 2) joined in 2022, so this employee will not be included.
- Employee 4 joined in 2023, and their manager (Employee 3) also joined in 2023.
- Employee 5 joined in 2021, and their manager (Employee 2) joined in 2022, so this employee will not be included.
The result of the query would be:
| EmployeeID | EmployeeJoinDate | ManagerID | ManagerJoinDate |
|---|---|---|---|
| 1 | 2022-05-15 | 2 | 2022-03-10 |
| 4 | 2023-04-05 | 3 | 2023-06-22 |
This result shows employees who joined in the same year as their managers.
You can run this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to retrieve the desired results.
Post a Comment