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
