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

 
Interview Questions & Answers
Last Post by SQL Admin 1 year ago
2 Posts
2 Users
0 Reactions
5,664 Views
RSS
Posts: 69
 Vinay Kumar
Topic starter
Nov 09, 2024 6:31 am
(@Vinay Kumar)
Joined: 6 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_idemployee_namejoining_date
1Alice2024-01-15
2Bob2024-05-20
3Charlie2023-11-10
4David2024-04-10
5Eve2023-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_idemployee_namejoining_date
2Bob2024-05-20
4David2024-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 1 year ago 2 times by SQL Admin
1 Reply
Posts: 134
 SQL Admin
Admin
Jan 09, 2025 3:49 pm
(@sql-admin)
Reputable Member
Joined: 6 years ago

🔍 How to Find Employees Who Joined in the Last 6 Months (SQL Example)

In this post, we'll write an SQL query to identify employees who joined an organization within the last 6 months. This query is essential for HR analytics, onboarding reports, and performance dashboards.

🧩 Problem Statement

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

💡 SQL Query


SELECT EMPLOYEE_ID, EMPLOYEE_NAME, JOIN_DATE
FROM EMPLOYEES
WHERE JOIN_DATE >= ADD_MONTHS(SYSDATE, -6);

🧠 Explanation

  • SELECT – Retrieves employee ID, name, and join date.
  • FROM – Source table (EMPLOYEES).
  • WHERE – Filters employees who joined within the last 6 months.
  • SYSDATE – Fetches the current system date.
  • ADD_MONTHS(SYSDATE, -6) – Calculates the date 6 months ago.

📚 Recommended SQL Learning Books:

As an Amazon Associate, we earn from qualifying purchases.

📋 Sample Table Structure


CREATE TABLE EMPLOYEES (
  EMPLOYEE_ID NUMBER PRIMARY KEY,
  EMPLOYEE_NAME VARCHAR2(100),
  JOIN_DATE DATE
);

🧾 Sample Data

EMPLOYEE_IDEMPLOYEE_NAMEJOIN_DATE
101John Smith2024-08-15
102Jane Doe2023-12-01
103Robert Brown2023-06-10
104Emily Davis2023-10-20

📈 Output Example (Assuming Today’s Date: 2025-01-09)

EMPLOYEE_IDEMPLOYEE_NAMEJOIN_DATE
101John Smith2024-08-15
102Jane Doe2023-12-01
104Emily Davis2023-10-20

⚙️ Optimized Variations

1️⃣ Filter by Department


SELECT EMPLOYEE_ID, EMPLOYEE_NAME, JOIN_DATE
FROM EMPLOYEES
WHERE JOIN_DATE >= ADD_MONTHS(SYSDATE, -6)
  AND DEPARTMENT_ID = 101;

2️⃣ Include Roles and Salaries


SELECT EMPLOYEE_ID, EMPLOYEE_NAME, ROLE, SALARY, JOIN_DATE
FROM EMPLOYEES
WHERE JOIN_DATE >= ADD_MONTHS(SYSDATE, -6);

3️⃣ Sort by Most Recent Joiners


SELECT EMPLOYEE_ID, EMPLOYEE_NAME, JOIN_DATE
FROM EMPLOYEES
WHERE JOIN_DATE >= ADD_MONTHS(SYSDATE, -6)
ORDER BY JOIN_DATE DESC;

📊 Practical Use Cases

  • HR Dashboards: Track recent joiners and hiring trends.
  • Onboarding Reports: Identify employees still under probation.
  • Power BI Integration: Create visuals of recent hires by department.

💡 Best Practices

  • Index JOIN_DATE: Improves query performance for large datasets.
  • Validate in Sandbox: Always test before production deployment.
  • Adjust for Time Zones: Use UTC for accurate reporting across locations.

✅ Conclusion

This query is a simple yet powerful way to analyze recent employee joiners in SQL. It can be used directly in analytics dashboards or integrated with Power BI to generate dynamic HR insights.

📢 Explore More: Check out Power BI Tools & Dashboard Reporting for more data visualization and automation techniques.


This post was modified 4 months 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: