Get employees list whose has completed 5,10,15 and so on years of service .i.e multiple of 5 years-CodePudding

I want to get employees list whose has completed 5,10,15 and so on years of service i.e multiple of 5 years. I should get the results from one month before. Suppose if 24-03-2022 an employee is going to complete his 5 or 10 or 15 years service then from 24-02-2022 to 24-03-2022. I should display their results, after 24-03-2022 results should not display. I have prepared a query which is showing results from 24-02-2022 to 24-03-2023. I want to display from 24-02-2022 to 24-03-2022. Please correct my query.

SELECT e.emp_id, e.first_name,eji.join_date 
FROM employee as e 
JOIN employee_job_info as eji on e.id = eji.employee_id 
WHERE 
    timestampdiff(YEAR, eji.join_date,DATE_ADD(curdate(), INTERVAL '1' MONTH)) % 5 = 0 
    AND floor(datediff('2022-02-24',eji.join_date)/365)!=0

CodePudding user response：

You need in something like this:

WITH RECURSIVE
cte AS (                                                  -- generate a table with values 5,10,...
    SELECT 5 experience
    UNION ALL
    SELECT experience   5 FROM cte WHERE experience < 50  -- 50 years seems to be enough
)
SELECT e.emp_id, e.first_name,eji.join_date, cte.experience 
FROM employee AS e 
JOIN employee_job_info AS eji ON e.id = eji.employee_id 
CROSS JOIN cte ON eji.join_date BETWEEN CURRENT_DATE - INTERVAL cte.experience YEAR
                                    AND CURRENT_DATE - INTERVAL cte.experience YEAR   INTERVAL 1 MONTH;