CodeCrack CodeCrack - 1 year ago 64
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.

Answer Source


    WHEN CURDATE() <= startDate THEN startDate + INTERVAL 3 year
    WHEN MOD((YEAR(CURDATE()) - YEAR(startDate)),3) = 0
          <= (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"

SQL Fiddle to show you it working with the test cases:!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!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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download