My database is SQL Server 2000. I am writing a SQL statement to find the number of Sick days
taken for this year. My issue is that the Anniversary date is the Hiredate. I just need to know the
number of the days taken for this year.
In The current anniversary year (part 1), we analyzed the problem and concluded that all we need is the date of the start of the current anniversary year. However, this could occur in either the current calendar year, or the previous.
An anniversary of the Hiredate occurs every calendar year, and always starts a new anniversary year. But if, in the current calendar year, the anniversary has already occurred, i.e. before today, as in the first timeline, then the current anniversary year started this calendar year, and extends into next year.
If the anniversary of the Hiredate has not occurred yet this calendar year, i.e. if it comes after today, as in the second timeline, then we're still in the anniversary year which started last calendar year.
So the solution hinges on determining the anniversary of the Hiredate in the current year. We will do this using date arithmetic, by subtracting the year of the Hiredate from the current year, then adding the difference back to the Hiredate. This effectively moves the Hiredate up into the current year, where it becomes the anniversary of the Hiredate.
Thanks, by the way, for mentioning the database system you're using. The syntax for date functions and date arithmetic varies greatly from one database system to the next. SQL Server's date functions are, like those of many database systems, proprietary. (This is due, by the way, to the fact that they were created before the SQL standards for temporal datatypes and operations.)
So using SQL Server's syntax,
DATEADD( year , year(GETDATE()) - year(Hiredate) , Hiredate )
The DATEADD function adds a number (second parameter) of years (first parameter) to a date (third parameter), and returns the resulting date. The number in the second parameter is an arithmetic expression, a subtraction of two year numbers.
We're almost finished, except for the tricky part. When the anniversary of the Hiredate in the current calendar year occurs after today, we're still in the anniversary year which started last calendar year, as in the second timeline, so we need to subtract one more year.
DATEADD( year , year(GETDATE()) - year(Hiredate) - case when month(Hiredate) > month(GETDATE()) or month(Hiredate) = month(GETDATE()) and day(Hiredate) > day(GETDATE()) then 1 else 0 end , Hiredate )
Notice how you can use a CASE expression right inside a SQL Server date function. Sweet, eh?
So now we have the date of the start of the employee's current anniversary year. To count sick days, we use this as one end of the date range, and today as the other.
select emplID , count(*) as sickdays_this_anniversary_year from Sickdays where sickDate between DATEADD( year , year(GETDATE()) - year(Hiredate) - case when month(Hiredate) > month(GETDATE()) or month(Hiredate) = month(GETDATE()) and day(Hiredate) > day(GETDATE()) then 1 else 0 end , Hiredate ) and GETDATE() group by emplID
This was first published in August 2004