Need suggestion to implement below scenario

[Diptansu11]

Hello Team , 

I need to implement below scenario , kindly help on finalising the approach to implement this. 

An email must be sent to some particular team to advise of new starters name and start date where the joining date is 3 working weeks away, or immediately if notified that starting date is less than 3 weeks away

I am thinking to create one analytics to fetch users whose joining date and sysdate() is 3weeks away. Then what need to do.

[Raghu]

@Diptansu11 

Create analytics report based criteria , create User group add particular team members , add it owner as user group
below query you need customize required columns add it

Select u.username,u.first from users u where date_sub(str_to_date(u.stratdate, '%m-%d-%Y'), interval 15 day) = DATE_FORMAT(now(), '%Y-%m-%d')

[Diptansu11]

I framed the query already need the design for this requirement

[Amit_Malik]

@Diptansu11 , You an take 2 approaches :

1) Use detective rule run job and run it daily. Write you query in rule and in actions send email

2) Update a CP using sav4sav and run rule to send notification

3) This I am not sure but I guess it will work as well, through Analytic update SAv4Sav account and run rule on CP change

Revert the CP value or you can even retain CP value as -is to know email was sent for these users.

Let me know if it helps, if does, Please ACCEPT SOLUTION and give KUDOS.

Thanks,

Amit

[rushikeshvartak · Accepted solution]

Steps :

1. Create analytics with username column in it
2. Attach email template to Report.
3. Schedule report everyday.
4. Create email template with below details

   To: ${userEmail}

   Subject: New Report available

   Body:

   Report - ${analyticsConfig.analyticsName} has been generated. Log in to EIC and navigate to the Analytics tab to access the report.

   ${ANALYTICSDATA}

Query :

SELECT u.username,
       u.firstname,
       u.lastname,
       u.startdate
FROM   users u
WHERE  Date_sub(Str_to_date(u.startdate, '%m-%d-%Y'), INTERVAL 3 week) <= Now();