Find Employees Who Joined on Same Date & Month: 3 Powerful SQL Methods

In any organization, it’s common to analyze employee data to find patterns and insights. One interesting analysis could be identifying employees who joined the company on the same date and month, regardless of the year. This can be useful for creating engagement programs, celebrating anniversaries, or just understanding hiring trends. In this blog post, we’ll explore how to find employees who share the same joining date and month using SQL.

1. Understanding the Problem

Imagine you have a table called employees with the following columns:

• employee_id: A unique number for each employee.
• employee_name: The name of the employee.
• joining_date: The date the employee joined the company.
• department: The department where the employee works.
• position: The employee’s job title.
• salary: The employee’s salary.
• location: The city where the employee is based.
• hire_type: The type of employment (e.g., Full-time, Part-time).
• experience_years: The number of years of experience the employee has.
• work_status: The current status of the employee (e.g., Active, Inactive).

Our task is to identify employees who joined the company on the same date and month, regardless of the year. This can help in various scenarios, such as:

• Anniversary Celebrations: Recognizing and celebrating work anniversaries for employees who joined on the same day and month.
• Team Building: Understanding hiring patterns and potentially grouping employees for team-building activities based on their joining date.
• Engagement Programs: Designing special programs or incentives for employees who share the same joining date.

To solve this problem, we’ll use SQL queries to group the employees by the month and day of their joining date, and then identify those who share the same date. We’ll explore multiple methods to achieve this, including using SQL functions and subqueries.

2. Creating the employees Table

We have an employees table that stores various details about each employee, including their joining date, name, department, position, and more. Here’s how our table is structured:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    joining_date DATE,
    department VARCHAR(50),
    position VARCHAR(50),
    salary DECIMAL(10, 2),
    location VARCHAR(100),
    hire_type VARCHAR(20),
    experience_years INT,
    work_status VARCHAR(20)
);

3. Inserting Sample Data

To show our queries, we’ll insert sample data into the employees table.

INSERT INTO employees (employee_id, employee_name, joining_date, department, position, salary, location, hire_type, experience_years, work_status)
VALUES 
(1, 'Amit Sharma', '2024-08-01', 'Sales', 'Sales Executive', 50000.00, 'Delhi', 'Full-time', 4, 'Active'),
(2, 'Priya Patel', '2024-08-01', 'Marketing', 'Marketing Manager', 70000.00, 'Mumbai', 'Full-time', 6, 'Active'),
(3, 'Ravi Kumar', '2024-08-02', 'IT', 'Software Engineer', 75000.00, 'Bengaluru', 'Full-time', 5, 'Active'),
(4, 'Neha Gupta', '2024-08-03', 'Finance', 'Accountant', 60000.00, 'Chennai', 'Full-time', 7, 'Active'),
(5, 'Suresh Reddy', '2024-08-03', 'HR', 'HR Specialist', 55000.00, 'Hyderabad', 'Full-time', 3, 'Active'),
(6, 'Anita Singh', '2024-08-01', 'IT', 'Data Analyst', 72000.00, 'Pune', 'Full-time', 4, 'Active'),
(7, 'Arun Verma', '2024-08-04', 'Sales', 'Sales Manager', 80000.00, 'Delhi', 'Full-time', 8, 'Active'),
(8, 'Sneha Joshi', '2024-08-05', 'IT', 'System Administrator', 68000.00, 'Mumbai', 'Full-time', 6, 'Active'),
(9, 'Vikram Mehta', '2024-08-06', 'Finance', 'Financial Analyst', 65000.00, 'Bengaluru', 'Full-time', 9, 'Active'),
(10, 'Sonia Agarwal', '2024-08-07', 'Marketing', 'Content Writer', 54000.00, 'Chennai', 'Full-time', 2, 'Active'),
(11, 'Kumar Sinha', '2024-08-08', 'IT', 'DevOps Engineer', 78000.00, 'Hyderabad', 'Full-time', 7, 'Active'),
(12, 'Kavita Iyer', '2024-08-09', 'HR', 'Recruiter', 60000.00, 'Pune', 'Full-time', 5, 'Active'),
(13, 'Ravi Desai', '2024-08-10', 'Sales', 'Sales Executive', 56000.00, 'Delhi', 'Full-time', 4, 'Active'),
(14, 'Swati Kapoor', '2024-08-11', 'Finance', 'Auditor', 67000.00, 'Mumbai', 'Full-time', 6, 'Active'),
(15, 'Rajesh Chandra', '2024-08-12', 'IT', 'Network Engineer', 75000.00, 'Bengaluru', 'Full-time', 7, 'Active'),
(16, 'Poonam Sharma', '2024-08-13', 'Marketing', 'SEO Specialist', 58000.00, 'Chennai', 'Full-time', 3, 'Active'),
(17, 'Manoj Rathi', '2024-08-14', 'HR', 'HR Manager', 72000.00, 'Mumbai', 'Full-time', 8, 'Active'),
(18, 'Meera Rao', '2024-08-15', 'Sales', 'Account Manager', 63000.00, 'Delhi', 'Full-time', 5, 'Active'),
(19, 'Ashok Bhardwaj', '2024-08-16', 'Finance', 'Treasurer', 70000.00, 'Bengaluru', 'Full-time', 10, 'Active'),
(20, 'Tanya Jain', '2024-08-17', 'IT', 'Software Engineer', 80000.00, 'Hyderabad', 'Full-time', 6, 'Active');

4. Method a: Using the DATE_FORMAT Function

The DATE_FORMAT function allows us to format the date to show only the month and day. We can then group by this formatted value to find employees who joined on the same date.

SELECT 
    DATE_FORMAT(joining_date, '%m-%d') AS join_month_day,
    GROUP_CONCAT(employee_name ORDER BY employee_name) AS employees
FROM 
    employees
GROUP BY 
    join_month_day
HAVING 
    COUNT(*) > 1;

SELECT 
    EXTRACT(MONTH FROM joining_date) AS join_month,
    EXTRACT(DAY FROM joining_date) AS join_day,
    GROUP_CONCAT(employee_name ORDER BY employee_name) AS employees
FROM 
    employees
GROUP BY 
    join_month, join_day
HAVING 
    COUNT(*) > 1;

SELECT 
    join_month_day,
    GROUP_CONCAT(employee_name ORDER BY employee_name) AS employees
FROM 
    (SELECT 
        CONCAT(EXTRACT(MONTH FROM joining_date), '-', EXTRACT(DAY FROM joining_date)) AS join_month_day,
        employee_name
     FROM employees) AS subquery
GROUP BY 
    join_month_day
HAVING 
    COUNT(*) > 1;