Hi,
We have below analytics:
SELECT
u.username,
u.displayname,
m.displayname as "Managers Name",
m.email AS 'Manager Email',
u.createdate,
u.enddate,
u.startdate,
u.customproperty34 AS "Training Status",
DATE_FORMAT(DATE_ADD(STR_TO_DATE(SUBSTRING_INDEX(u.customproperty34, '###', -1), '%d-%m-%Y'), INTERVAL 15 DAY), '%d-%m-%Y') AS "Due Date"
FROM
users u
LEFT JOIN
users m ON u.manager = m.userkey
WHERE
u.employeeType IN ("External Employee", "non_xyz_employee_external_user", "xyzbev_employee_intern_temp", "Managed Contractor", "Third Party", "Distributor")
AND STR_TO_DATE(DATE_FORMAT(u.enddate, '%d-%m-%Y'), '%d-%m-%Y') NOT BETWEEN STR_TO_DATE('01-11-2024', '%d-%m-%Y') AND STR_TO_DATE('20-12-2024', '%d-%m-%Y') AND u.createdate < STR_TO_DATE('11-10-2024', '%d-%m-%Y')
AND u.statuskey = 1
AND (m.email IS NULL OR m.email='')
But for some reason when we run the analytics it shows it's taking longer than expected. I believe its because of this particular condition in query: DATE_FORMAT(DATE_ADD(STR_TO_DATE(SUBSTRING_INDEX(u.customproperty34, '###', -1), '%d-%m-%Y'), INTERVAL 15 DAY), '%d-%m-%Y') AS "Due Date". It works when customproperty34 is null.
Also, we have below email template triggering through analytics.
Dear ${ANALYTICSDATA.'Managers Name'[0]},
In reference to our report, we have identified empty value for Manager Email attribute. The attached report includes the list of users with this issue. We expect you to complete the inconsistent data. Failure to do so will result in the deactivation of the user's account.
Thank you for your attention to this urgent issue.
Best regards,
xyz
The email doesn't trigger but when we give plain text the email is sent.
Can anyone please give suggestions here?
Thanks,
Vidhya
