Click HERE to see how Saviynt Intelligence is transforming the industry. |
10/21/2024 03:46 AM
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
10/21/2024 05:44 AM
@Vidhya try below query
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 Statusee",
u.userkey AS 'USERCONTEXT'
DATE_FORMAT(DATE_ADD(STR_TO_DATE(SUBSTRING_INDEX(u.customproperty34, '###', -1), '%d-%m-%Y'), INTERVAL 15 DAY), '%d-%m-%Y') AS "Due Datee"
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='')
10/21/2024 06:47 AM