currently writing a query to pull employee anniversary dates. so far i am able to pull the dates for 5, 10, 15 and 25 years but i am trying to only pull anniversary dates after the current date for each. any ideas?
SELECT
HireDate,
DATEADD(year, 5, HireDate) AS '5_Year_Anniversary',
DATEADD(year, 10, HireDate) AS '10_Year_Anniversary',
DATEADD(year, 15, HireDate) AS '15_Year_Anniversary',
DATEADD(year, 25, HireDate) AS '25_Year_Anniversary'
FROM
EMPTABLE;
SELECT
HireDate,
case when DATEADD(year, 5, HireDate) >= getdate() --check to see if the anniversary is on or after the current date
then DATEADD(year, 5, HireDate)
else NULL end AS '5_Year_Anniversary' --etc, repeat for your other columns
FROM
EMPTABLE
where DATEADD(year, 25, HireDate) >= getdate() --to filter out employees where all anniversary columns would be NULL
Using a case statement like above should work, depending on what value you want to display for these columns when the employee has already passed their anniversary. You may need to cast
/convert
your anniversary date if you want a specific value in those cases.
For example:
SELECT
HireDate,
case when DATEADD(year, 5, HireDate) >= getdate() --check to see if the anniversary is on or after the current date
then cast(DATEADD(year, 5, HireDate) as varchar(20))
else 'Already Passed' end AS '5_Year_Anniversary' --etc, repeat for your other columns
FROM
EMPTABLE
where DATEADD(year, 25, HireDate) >= getdate() --to filter out employees where all anniversary columns would be NULL