We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Need help with Analytics regarding End dates

PRATYUSH1
New Contributor III
New Contributor III

We are trying to create an analytic to list users whose end dates lie between current date and 30 days from current date. We were using the below analytic which was working before but after some minor changes, it has stopped working:

select u.username,u.customproperty16 as"Manager Email", u.email, u.employeetype, u.startdate,u.enddate, u.customproperty1 as "samaccountname", u.customproperty3 as "BU", u.customproperty13 as "CN" from users u where u.employeetype != 'Permanent Employee' and u.customproperty3='89' and u.enddate between CURDATE() and CURDATE()+30

We have also tried the below analytics but it is list users whose end dates are after current date but there is no limit for 30 days:

select u.username,u.customproperty16 as"Manager Email", u.email, u.employeetype, u.startdate,u.enddate, u.customproperty1 as "samaccountname", u.customproperty3 as "BU", u.customproperty13 as "CN" from users u where u.employeetype != 'Permanent Employee' and u.customproperty3='89' and u.enddate > CURDATE() AND u.enddate <  'CURDATE()+30'

 

We need some help to help us to understand what we are doing wrong and how to correct this.

1 REPLY 1

DixshantValecha
Saviynt Employee
Saviynt Employee

Hi @PRATYUSH1 

It seems that there is a syntax error in your second analytic. The CURDATE() function should not be enclosed in quotes in the second analytic.

You can try using the following analytic instead:

 

select u.username,u.customproperty16 as"Manager Email", u.email, u.employeetype, u.startdate,u.enddate, u.customproperty1 as "samaccountname", u.customproperty3 as "BU", u.customproperty13 as "CN" from users u where u.employeetype != 'Permanent Employee' and u.customproperty3='89' and u.enddate > CURDATE() AND u.enddate <  DATE_ADD(CURDATE(), INTERVAL 30 DAY)

 

Please let us know if further details  are needed on this.