Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

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

Gopinath
New Contributor III
New Contributor III

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

 

 

4 REPLIES 4

Raghu
Regular Contributor III
Regular Contributor III

@Gopinath 

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

 


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

armaanzahir
Valued Contributor
Valued Contributor

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)

 

 

Regards,
Md Armaan Zahir

Raghu
Regular Contributor III
Regular Contributor III

@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)


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

Gopinath
New Contributor III
New Contributor III

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

Kind Regards,

Gopinath