Birthdays & Years of Service

Variation on a theme – here we display weekly birthdays & service anniversaries.

Stored Procedure:

Procedure SP_Weekly
as
Select EM.Employee as EmpNum
, EmployeePhoto
, isNull(EM.PreferredName,EM.FirstName) + ' ' + RTRIM( EM.LastName) as EmployeeName
, RTRIM(datepart(month,custEmp_BirthDate)) + '/' +  RTRIM(datepart(day,custEmp_BirthDate)) as DateField
, 'Birthday'  as Notes
From EmployeeCustomTabFields ECTF
Join EM on (EM.Employee = ECTF.Employee)
Where
datepart(week,custEmp_BirthDate) = datepart(week,getdate())
And Status = 'A'
Union All
Select EM.Employee as EmpNum
, EmployeePhoto
, isNull(EM.PreferredName,EM.FirstName) + ' ' + RTRIM( EM.LastName) as EmployeeName
, RTRIM(datepart(month,HireDate)) + '/' +  RTRIM(datepart(day,HireDate))
  + '/' +  RTRIM(datepart(year,HireDate)) as DateField
, Cast(DateDiff(year, HireDate, getdate()) as varchar(12)) + RTRIM(' Years of Service' )  as Notes
From EmployeeCustomTabFields ECTF
Join EM on (EM.Employee = ECTF.Employee)
Where
datepart(week,HireDate) = datepart(week,getdate())
And Status = 'A'
Order By 4