Need information how to write if else condition based on day interval in email

[sk]

Hi Team,

We have a requirement where based on expiry date we need to have different email body.

Currently we are using triggering point as Analytics and binding variable ${ANALYTICSDATA. 'Expiry Date' [0]}.

So if we expiry date <= 4 days we need to have one email body else >= 4 different email body.

Can some one let me know the valid expression to achieve this.

Regards,

Sai Krishna 

[adriencosson]

Hi @sk,

Supposing your Date is in YYYY-MM-DD format, you may be able to use the following : 

${new Date().parse('yyyy-mm-dd', ANALYTICSDATA.'End Date'[0]).compareTo(new Date().plus(4))}

This would compare the End Date from report with Today's date + 4 days. If you want to go backward, you can use the .plus() method with negative numbers.

Adding reference to the "CompareTo" method. https://www.tutorialspoint.com/groovy/groovy_dates_times_compareto.htm 

[sk]

@adriencosson This was the format which was printing Jun 01, 2024 00:00:00

[rushikeshvartak]

Instead of changing email template. add one more column in report and add SQL logic and have different message.

[sk]

@rushikeshvartak Can you please elaborate more on what you suggested below was the query which I was using.

select U1.USERNAME,u.firstname as 'FIRST NAME',u.lastname as 'LAST NAME',a.name as 'Account Name',a.VALIDTHROUGH as 'Expiry Date' from users u
join users u1 on U1.USERKEY = U.MANAGER
join user_accounts ua on ua.userkey=u.userkey
join accounts a on a.accountkey=ua.accountkey
join endpoints e on e.endpointkey=a.endpointkey
where a.status in (1,'Manually Provisoned') and u.statuskey='1' AND U1.USERKEY = U.MANAGER and u.statuskey='1' and a.VALIDTHROUGH = DATE(NOW()) + INTERVAL -18 DAY and e.endpointkey='2'

[rushikeshvartak]

SELECT
U1.USERNAME,
u.firstname AS 'FIRST NAME',
u.lastname AS 'LAST NAME',
a.name AS 'Account Name',
a.VALIDTHROUGH AS 'Expiry Date',
CASE
WHEN DATEDIFF(a.VALIDTHROUGH, NOW()) <= 4 THEN 'Email Body 1'
ELSE 'Email Body 2'
END AS 'Email Body'
FROM
users u
JOIN
users u1 ON U1.USERKEY = U.MANAGER
JOIN
user_accounts ua ON ua.userkey = u.userkey
JOIN
accounts a ON a.accountkey = ua.accountkey
JOIN
endpoints e ON e.endpointkey = a.endpointkey
WHERE
a.status IN (1, 'Manually Provisoned')
AND u.statuskey = '1'
AND a.VALIDTHROUGH = DATE(NOW()) + INTERVAL -18 DAY
AND e.endpointkey = '2';