get employees list whose years of service are like 5,10,15,20 years-CodePudding

I have a requirement, where I have to display employees records who has completed 5 years, 10 years, 15 years. i.e multiple of 5 years. Here the employee records should display upto 1 year i.e if employee completed 5 years it should display from 5 years upto 6 years, after 6 years it should not display the results. I have one another condition, I should get the results of the employees before 3 months from reaching 5,10,15 years I have written a query, which showing records of all employees who has completed 5,10,15...years. I am not able to satisy my 2nd condition i.e getting results before 3 months. Below is my query. Any suggestion could be very helpful to me

SELECT e.emp_id, e.first_name, eji.join_date, e.last_name, e.status
  from employee as e
  JOIN employee_job_info as eji
    on e.id = eji.employee_id
 where floor(datediff('2021-12-15', eji.join_date) / 365)
 %5 = 0
   AND DATE_ADD(eji.join_date,
                INTERVAL floor(datediff('2021-12-15', eji.join_date) / 365) YEAR) BETWEEN
       '2021-09-15' AND
       DATE_ADD(DATE_ADD(eji.join_date,
                         INTERVAL
                         floor(datediff('2021-12-15', eji.join_date) / 365) YEAR),
                INTERVAL 1 YEAR)

CodePudding user response：

You can use: DATE_ADD(DATE_ADD(eji.join_date, INTERVAL floor(datediff('2021-12-15', eji.join_date) / 365) YEAR), INTERVAL 3 MONTH);

CodePudding user response：

This is sort of a "hack" that you can use.

Note: I'm assuming this 2021-12-15 is current date and hence I'm using curdate() in its place. You should too if that is the case.

Note2: From the problem statement, I get the idea that 5 years completion means between 4Y9M and 6Y, 10Y completion means 9Y9M and 11Y and so on.

Note3: From @Akina's comment -- using TIMESTAMPDIFF instead of datediff

SELECT e.emp_id, e.first_name, eji.join_date, e.last_name, e.status
FROM employee as e
  JOIN employee_job_info as eji
    on e.id = eji.employee_id
WHERE timestampdiff('YEAR', eji.join_date, curdate()) % 5 = 0 OR
   timestampdiff('YEAR', eji.join_date, DATE_ADD(curdate(), INTERVAL 3 MONTHS)) % 5 = 0