Find Employees Who Have Had the Same Job Title for More Than 5 Years Query Using MS SQL Server?

To find employees who have had the same job title for more than 5 years, you need to:

1. Identify the job title history for each employee.
2. Determine if an employee has held the same job title for more than 5 years.

Assuming you have an EmployeeHistory table that tracks job titles and their effective dates:

• EmployeeHistory:
  • EmployeeID
  • JobTitle
  • StartDate (date when the job title was assigned)
  • EndDate (date when the job title was changed, NULL if currently held)

Here’s a SQL query to find employees who have had the same job title for more than 5 years:

SQL Query

WITH JobTitleDurations AS (

    SELECT

        EmployeeID,

        JobTitle,

        StartDate,

        ISNULL(EndDate, GETDATE()) AS EndDate, -- Use current date if EndDate is NULL

        DATEDIFF(YEAR, StartDate, ISNULL(EndDate, GETDATE())) AS DurationYears

    FROM EmployeeHistory

),

LongTermTitles AS (

    SELECT

        EmployeeID,

        JobTitle,

        MAX(DurationYears) AS MaxDurationYears

    FROM JobTitleDurations

    GROUP BY EmployeeID, JobTitle

    HAVING MAX(DurationYears) > 5

)

SELECT

    e.EmployeeID,

    e.Name,

    l.JobTitle,

    l.MaxDurationYears

FROM LongTermTitles l

JOIN Employees e

    ON l.EmployeeID = e.EmployeeID

ORDER BY e.EmployeeID, l.JobTitle;

Explanation:

1. CTE JobTitleDurations:

   • ISNULL(EndDate, GETDATE()) AS EndDate: If EndDate is NULL (indicating the job title is currently held), use the current date (GETDATE()) for calculation.
   • DATEDIFF(YEAR, StartDate, ISNULL(EndDate, GETDATE())) AS DurationYears: Calculates the number of years the job title has been held.

2. CTE LongTermTitles:

   • MAX(DurationYears) AS MaxDurationYears: Finds the maximum duration for each JobTitle per employee.
   • HAVING MAX(DurationYears) > 5: Filters to include only those job titles held for more than 5 years.

3. Final Query:

   • JOIN Employees e ON l.EmployeeID = e.EmployeeID: Joins with the Employees table to get employee names.
   • ORDER BY e.EmployeeID, l.JobTitle: Orders the results by EmployeeID and JobTitle.

Example Data and Results

Assume you have the following data:

EmployeeHistory Table:

EmployeeID	JobTitle	StartDate	EndDate
1	Developer	2010-01-01	NULL
1	Senior Developer	2015-01-01	NULL
2	Analyst	2012-06-01	2020-06-01
2	Senior Analyst	2020-06-02	NULL

Employees Table:

EmployeeID	Name
1	Alice
2	Bob

The query would produce:

EmployeeID	Name	JobTitle	MaxDurationYears
1	Alice	Developer	13
1	Alice	Senior Developer	9

This result shows employees who have held the same job title for more than 5 years, along with the duration they held that title.

You can execute this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to find employees who have had the same job title for more than 5 years. Adjust the table and column names according to your actual database schema.