CodeCrack - 1 month ago 8
MySQL Question

# Calculating next 3 year anniversary of employee based on join date

I have an Employee mysql db table with employee name and start date. I want to be able to calculate upcoming 3 year anniversary of each employee from today's date. (So for example, Jordan's next upcoming anniversary would be 08/25/2019, Lee's would be 09/22/2017, Mike's 10/26/2018)

``````id    name       startDate
---------------------------
0     Jordan     08/25/2010
1     Lee        09/22/2014
2     Mike       10/26/2015
``````

Inefficient brute force solution would be to generate all anniversary dates from startDate up to current date plus one date beyond today's date and then select it.

but there is got to be a more efficient SQL Query to display next upcoming anniversary for each employee based on today's date. Must be a way to use DATEDIFF somehow.

EDITED

``````SELECT
*
,CASE
WHEN CURDATE() <= startDate THEN startDate + INTERVAL 3 year
WHEN MOD((YEAR(CURDATE()) - YEAR(startDate)),3) = 0
AND (CURDATE() - INTERVAL YEAR(CURDATE()) year)
<= (startDate - INTERVAL YEAR(startDate) year)
THEN startDate + INTERVAL (YEAR(CURDATE()) - YEAR(startDate)) year
ELSE startDate + INTERVAL (year(CURDATE()) - YEAR(startDate) + 3 - MOD(YEAR(CURDATE()) - YEAR(startDate),3)) year
END   as "Next3YrAniversary"
FROM
TableName
``````

SQL Fiddle to show you it working with the test cases: http://www.sqlfiddle.com/#!9/cf276a/1

Anyway, the idea is as follows:

• `startDate is Today or in future if so just add 3 years`
• `Remainder of (Yesrs Diff / 3) = 0` `and` `if current month/day is <= month/day of startDate` if so anniversary is this year and hasn't occurred yet, so `add number of years between current date and startDate to startDate`. To say it is in a slightly different way if it is `2016` and the anniversary is in `2016` it will see if the anniversary has happened yet this year or not.
• Else add to start date: `Number of years between Start Date & Now + 3 years - Remainder of (Years between Now and startDate / 3)`

A note about `YEAR()` vs `year`. you could write `year()` or `YEAR()` I tend to capitalize my specific functions etc but year() function just gets the `INT` of the year of a date. So `YEAR(CURDATE())` means get the year of the current date e.g. `2016`, `YEAR(startDate)` gets the year of the `startDate` e.g. `2013`. `MOD()` gives you the remainder from a division so `2 / 3 remainder 1`

``````2016 - 2013 gives you 3 so remainder of 3/3 is 0
``````

The lower case year which is not a function [without brackets] defines an INTERVAL type you could use day, year, month, etc.

``````date INTERVAL + 1 year = add 1 year to date
``````

Another example is

``````startDate INTERVAL + NumOfYearsDiff year = add NumOfYearsDiff to startDate
``````

In case 2 when comparing what I do is make both dates essentially `YEAR 0001` by subtracting the year integer out of the date and then compare which will tell me which date is later in the year to understand if the anniversary has already passed or is still coming up this year. `TIMESTAMPDIFF` does this automatically for you to a degree, but if you use then the aniversary date would be the next one e.g. `2019` if it is today in `2016`. So if you want to show it as today you would still have to check the same condition I did, meaning no real reason to use `TIMESTAMPDIFF`.

Here is a sqlfiddle side by side comparison of My Method, Spencer's, and Gordon's http://www.sqlfiddle.com/#!9/cf276a/3

What it shows:

• If the anniversary is today Gordon's & Spencer's methods will show the next anniversary as today + 3 years. Which could or could not be desired?
• Gordon's answer for future dates will show the startDate instead of startDate + 3 years