Setup
Go to Setup > Notification Service
2. Add New, give the task a name, define time and date settings. On Task Schedule tab, for Date setting > Repeat on Dates set the option “Daily“. Option “Repeat during the day“ should be off. For more details on the settings, please check Notification Service#ScheduleTask
3. Under the Notification E-mails tab, define Sender email and name.
Set the option “Dynamic from database“.
Set #Email in Field identifying email address and enter E-mail subject:
4. Insert Email Body. Note that you can insert screenshots, links and signatures to the mail body.
SQL Script
select
distinct
p01.pin #pin,
--FORMAT (p01.EMPLOYMENTSTARTDATE, 'dd/MM/yyyy') #Employmentstartdate,
p01.firstname #Firstname,
p01.lastname #Surname,
--Convert(date, dateadd(month, 5,EMPLOYMENTSTARTDATE)) startdate,
--Convert(date, getdate()) today,
--email.TELENO Employee_mail,
email.TELENO +',' + 'support@adonishr.com' #email,
--email.TELENO +',' + 'helen.ustych@adonishr.com' as #TestEmail
from
pw001p01 p01
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 NULLIF(email.TELENO, '') IS NOT NULL
and Convert(date, dateadd(month, 5,EMPLOYMENTSTARTDATE))= Convert(date,getdate ())