Work Anniversaries Coming Up in October 2025

Easy
230.

Work Anniversaries Coming Up in October 2025

EasyDatesBasics

MySQL

The people team sends a card on every work anniversary. For October 2025, list the current employees (exit_date is NULL) who joined in October of an earlier year — someone who joined in October 2025 has no anniversary yet.

Return emp_id, full_name, anniversary_date (the date in 2025 that falls on their joining day, as YYYY-MM-DD) and years_completed (2025 minus the joining year). Order the rows by anniversary_date, then emp_id.

Tables

Table: Employee

ColumnType
emp_idint
full_namevarchar
joined_ondate
exit_datedate

Primary key: emp_id.

exit_date is the last working day of someone who has left, or NULL for a current employee.

Example

The rows must come back in the order shown.

Employee

emp_idfull_namejoined_onexit_date
501Aditi Sharma2019-10-14NULL
502Rahul Nair2022-10-03NULL
503Sneha Patel2021-10-142024-06-30
504Liam Das2025-10-06NULL
505Riya Joshi2023-09-30NULL
506Nikhil Rao2024-10-14NULL

Output

emp_idfull_nameanniversary_dateyears_completed
502Rahul Nair2025-10-033
501Aditi Sharma2025-10-146
506Nikhil Rao2025-10-141
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

Work Anniversaries Coming Up in October 2025 — SQL Problem

The people team sends a card on every work anniversary. For October 2025, list the current employees (exit_date is NULL) who joined in October of an earlier…

  • Difficulty: Easy
  • Topics: Dates, Basics
  • Dialect: MySQL
  • Problem: #230

Problem statement

The people team sends a card on every work anniversary. For October 2025, list the current employees (exit_date is NULL) who joined in October of an earlier year — someone who joined in October 2025 has no anniversary yet.

Return emp_id, full_name, anniversary_date (the date in 2025 that falls on their joining day, as YYYY-MM-DD) and years_completed (2025 minus the joining year). Order the rows by anniversary_date, then emp_id.

Tables

Table: Employee

ColumnType
emp_idint
full_namevarchar
joined_ondate
exit_datedate

Primary key: emp_id.

exit_date is the last working day of someone who has left, or NULL for a current employee.

Examples

Example 1

Employee

emp_idfull_namejoined_onexit_date
501Aditi Sharma2019-10-14NULL
502Rahul Nair2022-10-03NULL
503Sneha Patel2021-10-142024-06-30
504Liam Das2025-10-06NULL
505Riya Joshi2023-09-30NULL
506Nikhil Rao2024-10-14NULL

Output

emp_idfull_nameanniversary_dateyears_completed
502Rahul Nair2025-10-033
501Aditi Sharma2025-10-146
506Nikhil Rao2025-10-141

How to solve Work Anniversaries Coming Up in October 2025

Filter on the parts of the joining date. exit_date IS NULL keeps current employees; MONTH(joined_on) = 10 keeps October joiners; YEAR(joined_on) < 2025 drops this October's new joiners, who have nothing to celebrate yet.

The years completed are 2025 - YEAR(joined_on). The anniversary date can be computed by moving the joining date forward that many years with DATE_ADD(joined_on, INTERVAL n YEAR) — formatted with DATE_FORMAT(…, '%Y-%m-%d') so the column is the plain text date the statement asks for — or assembled directly as CONCAT('2025-', DATE_FORMAT(joined_on, '%m-%d')). October has no 29 February problem, so both agree.

Because every anniversary is in October 2025, ordering by the anniversary date is ordering by the day of the month, which is how the alternative sorts; emp_id breaks ties between people who joined on the same day of different years. A single scan; date-part functions cannot use an index on joined_on, which is fine at HR-table sizes.

Reference solution (MySQL)

SELECT emp_id, full_name,
       DATE_FORMAT(DATE_ADD(joined_on, INTERVAL 2025 - YEAR(joined_on) YEAR), '%Y-%m-%d') AS anniversary_date,
       2025 - YEAR(joined_on) AS years_completed
FROM Employee
WHERE exit_date IS NULL AND MONTH(joined_on) = 10 AND YEAR(joined_on) < 2025
ORDER BY anniversary_date, emp_id

Another way

SELECT emp_id, full_name, CONCAT('2025-', DATE_FORMAT(joined_on, '%m-%d')) AS anniversary_date,
       YEAR('2025-10-01') - YEAR(joined_on) AS years_completed
FROM Employee
WHERE exit_date IS NULL AND DATE_FORMAT(joined_on, '%m') = '10' AND joined_on < '2025-01-01'
ORDER BY DAY(joined_on), emp_id

← Referral Bonus Owed to Each Employee · Salary Hike Percentage at the April 2025 Appraisal →