Forum

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

[Solved] Write an SQL Query to Find Employees Who Joined in the Last 6 Months

 
    Last Post
RSS

Posts: 69
 Vinay Kumar
Guest
Topic starter
Nov 09, 2024 6:31 am
(@Vinay Kumar)
Trusted Member
Joined: 5 years ago

Write an SQL Query to Find Employees Who Joined in the Last 6 Months

In this guide, we will learn how to find employees who joined within the last 6 months using SQL. We will use the DATEADD and INTERVAL functions based on the SQL database being used.

Understanding the Employees Table

Assume we have an employees table with the following structure:

employee_id employee_name joining_date
1 Alice 2024-01-15
2 Bob 2024-05-20
3 Charlie 2023-11-10
4 David 2024-04-10
5 Eve 2023-10-25

SQL Query for SQL Server (Using DATEADD)

SELECT employee_id, employee_name, joining_date 
FROM employees 
WHERE joining_date >= DATEADD(month, -6, GETDATE());

This query filters employees who joined within the last 6 months using DATEADD in SQL Server.

SQL Query for MySQL (Using INTERVAL)

SELECT employee_id, employee_name, joining_date 
FROM employees 
WHERE joining_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

In MySQL, we use DATE_SUB with CURDATE() to calculate the date 6 months ago.

Alternative Solution for Other Databases

SELECT employee_id, employee_name, joining_date 
FROM employees 
WHERE joining_date >= CURRENT_DATE - INTERVAL '6 months';

If your database does not support DATEADD or DATE_SUB, you can use date arithmetic as shown above.

Expected Output

employee_id employee_name joining_date
2 Bob 2024-05-20
4 David 2024-04-10

Conclusion

Using the above SQL queries, you can easily retrieve employees who joined in the last 6 months. Make sure to choose the appropriate function based on your SQL database.

For more SQL tips and best practices, check out our detailed guide on Power BI Tools.

This topic was modified 4 weeks ago by SQL Admin
1 Reply
Posts: 97
 SQL Admin
Admin
Jan 09, 2025 3:49 pm
(@sql-admin)
Estimable Member
Joined: 5 years ago

Here I’ll demonstrate how to write an SQL query to identify employees who joined an organization within the last 6 months. This type of query is crucial for HR analytics, onboarding processes, and performance reviews.

## Problem Statement
Find employees from the EMPLOYEES table who joined the company in the last 6 months based on their JOIN_DATE.

## SQL Query to Solve the Problem
### SQL Query:
```sql
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, JOIN_DATE
FROM EMPLOYEES
WHERE JOIN_DATE >= ADD_MONTHS(SYSDATE, -6);
```

### Explanation of the Query:
- **SELECT Clause**: Retrieves the employee ID, name, and joining date.
- **FROM Clause**: Specifies the EMPLOYEES table as the source.
- **WHERE Clause**: Filters rows where the JOIN_DATE is within the last 6 months.
- **SYSDATE**: Fetches the current date.
- **ADD_MONTHS(SYSDATE, -6)**: Calculates the date 6 months before the current date.

## Sample Table Structure
To use this query, ensure your EMPLOYEES table has a structure like this:
```sql
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID NUMBER PRIMARY KEY,
EMPLOYEE_NAME VARCHAR2(100),
JOIN_DATE DATE
);
```

## Sample Data
Here’s an example dataset for demonstration:

| EMPLOYEE_ID | EMPLOYEE_NAME | JOIN_DATE |
|-------------|--------------|------------|
| 101 | John Smith | 2024-08-15 |
| 102 | Jane Doe | 2023-12-01 |
| 103 | Robert Brown | 2023-06-10 |
| 104 | Emily Davis | 2023-10-20 |

## Output of the Query
Assuming today’s date is **2025-01-09**, the output will be:

| EMPLOYEE_ID | EMPLOYEE_NAME | JOIN_DATE |
|-------------|--------------|------------|
| 101 | John Smith | 2024-08-15 |
| 102 | Jane Doe | 2023-12-01 |
| 104 | Emily Davis | 2023-10-20 |

## Optimized Variations of the Query

### 1. Filter Specific Departments
If the EMPLOYEES table has a **DEPARTMENT_ID** column, find recent joiners in a specific department:
```sql
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, JOIN_DATE
FROM EMPLOYEES
WHERE JOIN_DATE >= ADD_MONTHS(SYSDATE, -6) AND DEPARTMENT_ID = 101;
```

### 2. Include Additional Columns
To display employee roles or salaries:
```sql
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, ROLE, SALARY, JOIN_DATE
FROM EMPLOYEES
WHERE JOIN_DATE >= ADD_MONTHS(SYSDATE, -6);
```

### 3. Order by Join Date
To sort the results by the most recent joiners:
```sql
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, JOIN_DATE
FROM EMPLOYEES
WHERE JOIN_DATE >= ADD_MONTHS(SYSDATE, -6)
ORDER BY JOIN_DATE DESC;
```

## Practical Applications
- **HR Dashboards**: Visualize trends of new hires over the past 6 months.
- **Onboarding Reports**: Track employees who are still in their probation period.
- **Team Analytics**: Analyze recent joiners' impact on team performance.

## Best Practices
- **Index the JOIN_DATE Column**: Improves query performance, especially for large datasets.
- **Test in Development Environment**: Always validate the query with sample data before deploying it in production.
- **Consider Time Zones**: Ensure date calculations account for time zone differences in global databases.

## Conclusion
This SQL query offers a straightforward and efficient way to track employees who joined recently. Whether for HR analysis or reporting purposes, it’s a valuable tool for managing workforce data.

---

### Additional Resources
Check out more SQL best practices and Power BI insights here: [Power BI Tools & Dashboard Reporting]

This post was modified 4 weeks ago 2 times by SQL Admin
Reply

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Share: