The notification task sends out an email to selected email addresses when a birthday anniversary is coming up.
Keywords
Birthday, Anniversary
Description
The notification sends out an email to selected email addresses when a birthday anniversary is coming up.
The notification is sent 30 days before the anniversary.
The anniversary age is 20, 30, 40, 50, 60, 70, 80, 90 years old.
Sample Set Up
SQL Statement
Don’t forget to replace test.email with the proper e-mail of the receiver/manager.
DECLARE @DAYS INT = 30 --Notify before the Birthday
SELECT p01.PIN as #Pin,
p01.Firstname as #Firstname,
p01.Lastname as #Lastname,
p01.Name AS #FullName,
CONVERT(VARCHAR, p01.BIRTHDATE, 107) AS #Birthdate,
(CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.BIRTHDATE, 112)) / 10000 as #Age,
--email.TELENO AS #Email,
'test.email' as #Email,
org.NAME as #Company
FROM pw001p01 p01
Join PWORG org on p01.client = org.numorgid
and orgtype = 2
--and org.numorgid IN (23,10000606,10000607,10000643,10000648,10000649,10000650,10000651,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
)
)
-- order by 1
WHERE (CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.BIRTHDATE, 112)) / 10000
in (20,30,40,50,60,70,80,90) --Age filter
and
DATEADD(YY,(CONVERT(INT, CONVERT(CHAR(8),DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE())), 112)) - CONVERT(CHAR(8), p01.BIRTHDATE, 112)) / 10000, DATEDIFF(day, 0, p01.BIRTHDATE)) = DATEADD(day,@Days, DATEDIFF(day, 0, GETDATE()))
and p01.EMPLOYMENTENDDATE is null
Fields Description
Field
Description
Field
Description
#Pin
The PIN number.
#Firstname
The First Name of the person.
#Lastname
The Last Name of the person.
#FullName
The full Name of the person.
#Birthdate
The birthday of the person.
#Age
The age of the person.
#Email
The e-mail of the manager(s).
#Company
The employment company of the person from Personal Details.
Sample Email
Dear Receiver Name,
Please be informed that [#Firstname] [#Lastname] will have a [#Age] years Birthday Anniversary coming up in 30 days.
