2

basically trying to create a query that will display employee anniversary dates for upcoming month or year of current date, also would like to display a column that shows the years of service

SELECT
Employee,
Hire_Date

CASE WHEN DATEADD(YY,DATEDIFF(yy,Hire_Date,GETDATE()),Hire_Date)<GETDATE() THEN DATEDIFF(yy,Hire_Date,GETDATE())
ELSE DATEDIFF(yy,Hire_Date,GETDATE())-1 END AS 'Years of service'

FROM
MyTable

looking to display employees with anniversary dates coming up in the coming month or in the next year

Teddy
  • 33
  • 7
  • 1
    Welcome to Stack Overflow! Please edit your question to include your table definitions, sample data, and expected output (as text, not pictures, please), so that the people here can see what we're working with. See [ask] and [mcve] for additional details. – Brian Apr 02 '19 at 16:19

2 Answers2

0

Here is the script validated (see pciture below) to display the employees with birth_date coming in the next month

Replace mytable by your own table

declare @mytable as table(employe varchar(100), birth_date datetime,hire_date datetime)

insert into @mytable values
('name1','01/01/1972','01/01/2000') ,
('name2','12/02/1985','01/02/2003') ,
('name3','04/12/1990','03/04/2005') ,
('name4','05/03/1969','12/12/2005') ,
('name5','04/02/1968','12/02/2010') ,
('name6','04/04/1968','12/11/2009') ,
('name7','12/03/1978','01/01/2019') ,
('name8','01/12/2000','03/02/2018') ,
('name9','12/12/1970','05/02/2019') ,
('name10','04/04/1980','04/04/2018') 

select employe,birth_date,hire_date,
CASE WHEN DATEADD(YY,DATEDIFF(yy,Hire_Date,GETDATE()),Hire_Date)<GETDATE() THEN DATEDIFF(yy,Hire_Date,GETDATE())
ELSE DATEDIFF(yy,Hire_Date,GETDATE())-1 END AS 'Years of service'
from @mytable where (
month(getdate()) < 12 
and 
month(birth_date)=1+month(getdate()) )
or (month(getdate())=12 and month(birth_date)=1)

enter image description here

Kemal AL GAZZAH
  • 967
  • 6
  • 15
  • thank you for that answer, very helpful, so how different would the where clause be if i wanted to see the results for the next year? – Teddy Apr 02 '19 at 18:08
  • the next month will be in the next year if your current month is 12, that's why I putted two cases current month <12 and current month=12 – Kemal AL GAZZAH Apr 02 '19 at 18:11
  • is there a way to only display certain increments of years of service column? i.e. 5 year, 10 year, 15 year, Etc. – Teddy Apr 02 '19 at 20:33
0

I don't understand well, but, if you need know who make anniversary in the next month, or next year, you should use DATEDIFF function for to filter the data.

Example:

SELECT Employee, Hire_Date, DATEDIFF(year, Hire_Date, getdate()) as YearsService
FROM MyTable
-- if you need fetch to next month, you should use <= 1
WHERE DATEDIFF(month, CONCAT(YEAR(GETDATE()), '-', MONTH(Hire_Date), '-' , DAY(Hire_Date)), GETDATE()) = 1
Ramon Costa
  • 59
  • 1
  • 1