CODEWITHSUNDEEP
mysqlsql
user3300460
user3300460

Reputation: 1

MySQL query to get next month's employee jubilees

I want to write a query to get list of Milestone achievers (employees) due in next month. For example, In January we should be able to see the February Milestone achievers.

Milestone achievers means "Employees who will complete 5,10,15,20,25,30,35,40 and so on" years in company in next month.

Can anyone please assist?

Thank you so much in advance !

Upvotes: 0

Views: 97

Answers (1)

fancyPants
fancyPants

Reputation: 51888

Given that you have a table employees with a hired_at column, containing the date, when they began working in the company, you just have to get the rows where the difference between next month and their hire date is a multiple of 60 months (5 years):

SELECT employee_name, 
TIMESTAMPDIFF(MONTH, DATE_FORMAT(hired_at, '%Y-%m-01'), DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01')) / 60 AS how_many_years
FROM employees
WHERE 
TIMESTAMPDIFF(MONTH, DATE_FORMAT(hired_at, '%Y-%m-01'), DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01')) % 60 = 0;

I'm using TIMESTAMPDIFF with the unit MONTH to calculate the difference and DATE_FORMAT to set both dates to the first of the month. This may not be necessary, but you're on the safe side with it.

The % 60 = 0 is the modulo operation (%). If the previous is dividable through 60 with nothing left (0), then we have a multiple of 60 months / 5 years. Just dividing the difference through 60 in the SELECT clause will give you how many years the employee is working for the company.

  • read more about timestampdiff here and date_format here

Upvotes: 2

Related Questions