...
| Page Properties |
|---|
Summary | The notificatin notification task sends out an email to selected email addresses when a birthday anniversary is coming up. |
|---|
Keywords | Birthday, Anniversary |
|---|
|
Description
Sends 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.
| Expand |
|---|
|
| Code Block |
|---|
DECLARE @DAYS INT = 30 --Notify before the Birthday
SELECT p01.PIN as #Pin,
p01.Firstname as #Firstname,
p01.Lastname as #Lastname,
p01.Name AS |
#Name#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,
' |
sigridkviteberg@adonis.noemail' 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 ( |
10,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 |
|---|
#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.
...
