Forum

Forums
SQL
Interview Questions...
How to Write an SQL...
 
Share:
Notifications
Clear all

How to Write an SQL Query to Display the Working Period of Each Employee

 
    Last Post
RSS

Posts: 81
 SQL Admin
Admin
Topic starter
Jan 10, 2025 5:31 am
(@sql-admin)
Estimable Member
Joined: 4 years ago

Calculating the working period of employees is a common requirement in HR systems and databases to analyze employee tenure or eligibility for benefits. In this guide, we will write an SQL query to calculate the working period of each employee, demonstrate step-by-step implementation, and discuss variations for specific use cases.

Understanding the Problem

The working period of an employee is the duration between their joining date and the current date or their resignation date. To calculate this, we use date-related SQL functions. Consider the following table structure:

Column Description
EmployeeID Unique identifier for each employee
EmployeeName Name of the employee
JoiningDate Date when the employee joined
ResignationDate Date when the employee resigned (nullable)

Example Dataset

Here is an example dataset:

EmployeeID EmployeeName JoiningDate ResignationDate
1 Alice 2018-05-15 NULL
2 Bob 2016-03-10 2022-07-31
3 Charlie 2020-11-01 NULL
4 Diana 2019-02-20 2023-01-15

SQL Query to Calculate Working Period

Here’s the query to calculate the working period for each employee:

sql
 
SELECT
EmployeeID,
EmployeeName,
JoiningDate,
COALESCE(ResignationDate, CURRENT_DATE) AS EndDate,
TIMESTAMPDIFF(YEAR, JoiningDate, COALESCE(ResignationDate, CURRENT_DATE)) AS WorkingYears,
TIMESTAMPDIFF(MONTH, JoiningDate, COALESCE(ResignationDate, CURRENT_DATE)) % 12 AS WorkingMonths,
TIMESTAMPDIFF(DAY, DATE_ADD(JoiningDate, INTERVAL TIMESTAMPDIFF(YEAR, JoiningDate, COALESCE(ResignationDate, CURRENT_DATE)) YEAR),
DATE_ADD(JoiningDate, INTERVAL TIMESTAMPDIFF(MONTH, JoiningDate, COALESCE(ResignationDate, CURRENT_DATE)) MONTH)) AS WorkingDays
FROM Employees;

Step-by-Step Explanation

  1. COALESCE(ResignationDate, CURRENT_DATE) AS EndDate:

    • Uses the ResignationDate if available; otherwise, uses the current date (CURRENT_DATE).

  2. TIMESTAMPDIFF(YEAR, JoiningDate, EndDate) AS WorkingYears:

    • Calculates the number of full years between JoiningDate and EndDate.

  3. TIMESTAMPDIFF(MONTH, JoiningDate, EndDate) % 12 AS WorkingMonths:

    • Calculates the total months and uses modulo (%) to find the remaining months after full years.

  4. TIMESTAMPDIFF(DAY, ... ) AS WorkingDays:

    • Calculates the remaining days after extracting full years and months using intermediate calculations.

Result

Using the above query, here’s the output for the example dataset:

EmployeeID EmployeeName JoiningDate EndDate WorkingYears WorkingMonths WorkingDays
1 Alice 2018-05-15 2025-01-10 6 7 26
2 Bob 2016-03-10 2022-07-31 6 4 21
3 Charlie 2020-11-01 2025-01-10 4 2 9
4 Diana 2019-02-20 2023-01-15 3 10 25

Alternative Queries

1. Simplified Query for Total Days

If you only need the total days worked:

sql
 
SELECT
EmployeeID,
EmployeeName,
JoiningDate,
COALESCE(ResignationDate, CURRENT_DATE) AS EndDate,
DATEDIFF(COALESCE(ResignationDate, CURRENT_DATE), JoiningDate) AS TotalDaysWorked
FROM Employees;

2. Using INTERVAL for Exact Date Calculations

For databases without TIMESTAMPDIFF (e.g., SQL Server):

sql
 
SELECT
EmployeeID,
EmployeeName,
JoiningDate,
COALESCE(ResignationDate, GETDATE()) AS EndDate,
DATEDIFF(YEAR, JoiningDate, COALESCE(ResignationDate, GETDATE())) AS WorkingYears
FROM Employees;

Use Cases

  1. Employee Analytics:
    Calculate tenure for promotions or benefits eligibility.

  2. Resignation Analysis:
    Understand patterns in resignation timelines.

  3. HR Dashboards:
    Display employee working periods visually in dashboards.

Performance Considerations

  • Indexing:
    Ensure JoiningDate and ResignationDate are indexed for faster queries.

  • NULL Handling:
    Use COALESCE or similar functions to avoid errors with NULL values.

  • Database Compatibility:
    The syntax for date functions may vary across databases. Adapt the query accordingly.

Conclusion

Calculating the working period of each employee is a vital operation for many HR and business applications. The SQL query provided here demonstrates how to compute this efficiently, with considerations for both active and resigned employees. For more SQL solutions, join our SQL forum and connect with other SQL enthusiasts.

For additional learning, check out this SQL Date Functions guide.

Topic Tags
SQL query working period calculate employee tenur SQL date difference quer working period calculati SQL HR analytics query

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Related Topics
Topic Tags:  SQL query working period (2) , calculate employee tenur (2) , SQL date difference quer (2) , working period calculati (2) , SQL HR analytics query (2) ,
Share: