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: 20, 30, 40, 50, or 60 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) --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.