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
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
if current month/day is <= month/day of startDateif 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
2016and the anniversary is in
2016it will see if the anniversary has happened yet this year or not.
Number of years between Start Date & Now + 3 years - Remainder of (Years between Now and startDate / 3)
A note about
year. you could write
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.
YEAR(startDate) gets the year of the
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
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: