Forum

Forums
SQL
Interview Questions...
"SQL Query to Calcu...
 
Share:
Notifications
Clear all

"SQL Query to Calculate Employee Working Period"

 
Interview Questions & Answers
Last Post by SQL Admin 1 year ago
1 Posts
1 Users
0 Reactions
673 Views
RSS
Posts: 134
 SQL Admin
Admin
Topic starter
Jan 10, 2025 5:35 am
(@sql-admin)
Reputable Member
Joined: 6 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:

ColumnDescription
EmployeeIDUnique identifier for each employee
EmployeeNameName of the employee
JoiningDateDate when the employee joined
ResignationDateDate when the employee resigned (nullable)

Example Dataset

Here is an example dataset:

EmployeeIDEmployeeNameJoiningDateResignationDate
1Alice2018-05-15NULL
2Bob2016-03-102022-07-31
3Charlie2020-11-01NULL
4Diana2019-02-202023-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:

EmployeeIDEmployeeNameJoiningDateEndDateWorkingYearsWorkingMonthsWorkingDays
1Alice2018-05-152025-01-106726
2Bob2016-03-102022-07-316421
3Charlie2020-11-012025-01-10429
4Diana2019-02-202023-01-1531025

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.


This topic was modified 1 year ago by SQL Admin
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: