Summary	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.

sql statement

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
#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.