Summary	The notification task sends out an email to selected email addresses when the employment anniversary comes.
Keywords	Employment, Anniversary, Seniority

Description

The notification task sends out an email to selected email addresses when the employment anniversary comes.

The notification is sent to the manager 30 days before the anniversary.

The anniversary years considered 5,10,15,20,25,30,35,40,45,50,55,60,65,70

Sample Set Up

SQL Statement

Declare @Days INT = 30 --Notify before the date of Anniversary

SELECT P01.PIN,
       p01.NAME as #FullName,
       p01.FIRSTNAME as #Firstname,
       p01.LASTNAME as #Lastname,
	   p01.EMPLOYMENTSTARTDATE as #EmploymentStartDate,
	   (CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.EMPLOYMENTSTARTDATE, 112)) / 10000 as #YearsInService,
	   CONVERT(VARCHAR(15),DATEADD(YYYY,(CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.EMPLOYMENTSTARTDATE, 112)) / 10000, DATEDIFF(day, 0, p01.employmentstartdate)),107) #AnniversaryDate,
	   org.Name as #Company,
--	   email.TELENO as #Email
'test.e-mail' as #Email

FROM PW001P01 P01
Join PWORG org on p01.CLIENT = org.NUMORGID and org.ORGTYPE = 2 --and org.NUMORGID in (23,10000606,10000607,10000643,10000648,10000649,10000650,10000662,10000668,10000669) --Include the Numorgid of the companies
LEFT JOIN PW001P0T email ON  email.PIN = p01.PIN AND email.TELETYPE = 6 AND email.TELEPRIORITY IS NOT NULL AND NOT EXISTS (SELECT 1  FROM   PW001P0T t WHERE  t.PIN = email.PIN AND t.TELETYPE = email.TELETYPE AND t.TELEPRIORITY IS NOT NULL
                           AND (t.TELEPRIORITY < email.TELEPRIORITY OR t.TELEPRIORITY = email.TELEPRIORITY AND t.SEQUENCENO < email.SEQUENCENO))
WHERE  (CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.EMPLOYMENTSTARTDATE, 112)) / 10000 
	   in (5,10,15,20,25,30,35,40,45,50,55,60,65,70) --Years in Service
	   and DATEADD(YY,(CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.EMPLOYMENTSTARTDATE, 112)) / 10000, DATEDIFF(day, 0, p01.employmentstartdate)) = DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE()))
	   and p01.EMPLOYMENTENDDATE is null

Fields Specification

Field	Description
#FullName	The full Name of the person.
#Firstname	The First Name of the person.
#Lastname	The Last Name of the person.
#EmploymentStartDate	The Employment Start Date of the person.
#YearsInService	The number of years worked for the company.
#AnniversaryDate	The anniversary celebration date date.
#Company	The employment company from Personal Details.
#Email	The e-mail of the receiver/manager.

Sample Email

Dear Receiver Name,

Please be informed that [#Firstname] [#Lastname] will have a [#YearsInService] years Employment Anniversary coming up on the [#AnniversaryDate]