Find All Employees Who Joined in the Same Year as Their Manager

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:
  • EmployeeID
  • ManagerID
  • JoinDate

Here’s how you can write an SQL query to find all employees who joined in the same year as their manager:

SQL Query

SELECT e.EmployeeID, e.JoinDate AS EmployeeJoinDate, m.EmployeeID AS ManagerID, m.JoinDate AS ManagerJoinDate

FROM Employees e

JOIN Employees m ON e.ManagerID = m.EmployeeID

WHERE YEAR(e.JoinDate) = YEAR(m.JoinDate);

Explanation:

1. Self-Join:

   • The Employees table is joined with itself to relate employees with their managers using the condition e.ManagerID = m.EmployeeID.

2. 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.

https://mytecbooks.blogspot.com/2024/08/find-all-employees-who-joined-in-same.html

Find All Employees Who Joined in the Same Year as Their Manager Using MS SQL Server Query programs interview questions answers freshers experience