...
| Page Properties | ||||
|---|---|---|---|---|
|
Description
The notification task sends out an email to selected email addresses when the employment anniversary comes.
...
The anniversary years considered 5,10,15,20,25,30,35,40,45,50,55,60,65,70
Sample Set Up
...
SQL Statement
| Code Block |
|---|
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]
...