The SQL query functions correctly in the Data Analyzer but encounters issues when used in Analytics

[Gopinath]

Hi Team,

The provided query functions correctly within the Data Analyzer under the Admin section and also produces the expected results in "Preview" mode under Analytics in Intelligence. However, when attempting to create the analytics, it results in a zero count.

Despite trying various formats, all the queries perform well in the Data Analyzer but encounter issues specifically in the Analytics section.
Can you please suggest modifications to the query to address the issue?

Query1:

SELECT
u.username,
u.displayname,
u.EMPLOYEETYPE,
u.companyname,
datediff(STR_TO_DATE(DATE_FORMAT(u.startdate,'%Y-%m-%d'),'%Y-%m-%d'),STR_TO_DATE(DATE_FORMAT(curdate(),'%Y-%m-%d'),'%Y-%m-%d')) as 'Startdate',
datediff(STR_TO_DATE(DATE_FORMAT(u.enddate,'%Y-%m-%d'),'%Y-%m-%d'),STR_TO_DATE(DATE_FORMAT(curdate(),'%Y-%m-%d'),'%Y-%m-%d')) as 'Enddate',
u.departmentname,
u.country,
um.email AS ManagerEmail,
um.customproperty7 AS ManagerVIPStatus
FROM
users u
INNER JOIN
users um ON u.manager = um.userkey
WHERE
u.EMPLOYEETYPE = 'guest'
AND u.statuskey = 1
AND datediff(STR_TO_DATE(DATE_FORMAT(u.enddate,'%Y-%m-%d'),'%Y-%m-%d'),STR_TO_DATE(DATE_FORMAT(curdate(),'%Y-%m-%d'),'%Y-%m-%d'))= 4

Query2:

SELECT u.username, u.displayname, u.EMPLOYEETYPE, u.companyname, DATE_FORMAT(u.startdate, '%Y-%m-%d') AS 'Startdate', DATE_FORMAT(u.enddate, '%Y-%m-%d') AS 'enddate', u.departmentname, u.country, um.email AS ManagerEmail, um.customproperty7 AS ManagerVIPStatus FROM users u INNER JOIN users um ON u.manager = um.userkey WHERE u.EMPLOYEETYPE = 'guest' AND u.statuskey = 1 AND DATE_FORMAT(u.enddate, '%Y-%m-%d') = DATE_ADD(CURRENT_DATE(), INTERVAL +4 DAY)

Query3:

select u.username,u.displayname,u.EMPLOYEETYPE,u.companyname,DATE_FORMAT(u.startdate,'%Y-%m-%d') AS 'Startdate',DATE_FORMAT(u.enddate,'%Y-%m-%d') AS 'enddate',u.departmentname,u.country,um.email as ManagerEmail,um.customproperty7 as ManagerVIPStatus from users u , users um where u.manager=um.userkey and u.EMPLOYEETYPE='guest' and u.statuskey=1 and DATE_FORMAT(u.enddate,'%Y-%m-%d') = DATE_ADD(CURRENT_DATE(),INTERVAL +4 DAY)

Thanks,

Gopinath

[CR · Accepted solution]

@Gopinath 

New version 'Date' name it won't support , can you change where ever date column and give  other alias name  instead of 'date'

[armaanzahir · Accepted solution]

Hi @Gopinath 

Try using the below query. the start and end date alias needs to be changed.

select u.username,u.displayname,u.EMPLOYEETYPE,u.companyname,DATE_FORMAT(u.startdate,'%Y-%m-%d') AS 'Startdte',DATE_FORMAT(u.enddate,'%Y-%m-%d') AS 'enddte',u.departmentname,u.country,um.email as ManagerEmail,um.customproperty7 as ManagerVIPStatus from users u , users um where u.manager=um.userkey and u.EMPLOYEETYPE='guest' and u.statuskey=1 and DATE_FORMAT(u.enddate,'%Y-%m-%d') = DATE_ADD(CURRENT_DATE(),INTERVAL +4 DAY)

[CR · Accepted solution]

@Gopinath 

Try like below:

SELECT
u.username,
u.displayname,
u.EMPLOYEETYPE,
u.companyname,
datediff(STR_TO_DATE(DATE_FORMAT(u.startdate,'%Y-%m-%d'),'%Y-%m-%d'),STR_TO_DATE(DATE_FORMAT(curdate(),'%Y-%m-%d'),'%Y-%m-%d')) as 'Startdte',
datediff(STR_TO_DATE(DATE_FORMAT(u.enddate,'%Y-%m-%d'),'%Y-%m-%d'),STR_TO_DATE(DATE_FORMAT(curdate(),'%Y-%m-%d'),'%Y-%m-%d')) as 'Endte',
u.departmentname,
u.country,
um.email AS ManagerEmail,
um.customproperty7 AS ManagerVIPStatus
FROM
users u
INNER JOIN
users um ON u.manager = um.userkey
WHERE
u.EMPLOYEETYPE = 'Vendor'
AND u.statuskey = 1
AND datediff(STR_TO_DATE(DATE_FORMAT(u.enddate,'%Y-%m-%d'),'%Y-%m-%d'),STR_TO_DATE(DATE_FORMAT(curdate(),'%Y-%m-%d'),'%Y-%m-%d'))= 4

Query2:

SELECT u.username, u.displayname, u.EMPLOYEETYPE, u.companyname, DATE_FORMAT(u.startdate, '%Y-%m-%d') AS 'Startdtte', DATE_FORMAT(u.enddate, '%Y-%m-%d') AS 'enddtte', u.departmentname, u.country, um.email AS ManagerEmail, um.customproperty7 AS ManagerVIPStatus FROM users u INNER JOIN users um ON u.manager = um.userkey WHERE u.EMPLOYEETYPE = 'guest' AND u.statuskey = 1 AND DATE_FORMAT(u.enddate, '%Y-%m-%d') = DATE_ADD(CURRENT_DATE(), INTERVAL +4 DAY)

Query3:

select u.username,u.displayname,u.EMPLOYEETYPE,u.companyname,DATE_FORMAT(u.startdate,'%Y-%m-%d') AS 'Startdtte',DATE_FORMAT(u.enddate,'%Y-%m-%d') AS 'enddtte',u.departmentname,u.country,um.email as ManagerEmail,um.customproperty7 as ManagerVIPStatus from users u , users um where u.manager=um.userkey and u.EMPLOYEETYPE='guest' and u.statuskey=1 and DATE_FORMAT(u.enddate,'%Y-%m-%d') = DATE_ADD(CURRENT_DATE(),INTERVAL +4 DAY)

[Gopinath]

Thank you, @CR and @armaanzahir , for the prompt assistance. Following your suggestions, I modified the alias name 'date,' and the outcome met expectations.

Kind Regards,

Gopinath