Employment Anniversary Reminder (send to the manager)
- Natalya Miroshnychenko
- Daria Chebotaieva
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. |
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]