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

Send notification for last 1 hour completed Requests

geet14
New Contributor III
New Contributor III

Hi Team,

Use case : Send notification after completion of requests for last 1 hour. I am trying below query but for column completedate it's seems its not working. Can you please suggest.

SELECT distinct u.username,u.email,(SELECT DISTINCT SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid))) AS 'Request ID' , AR.ENDPOINTASCSV AS 'APPLICATION',m.userkey as requestee,m.firstname as requesteefirstname,m.email as requesteeemail,AR.REQUESTDATE as 'Request Date',AR.status as requeststatus,AR.COMPLETEDATE as requestcompleteddate
FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA, users u,users m
WHERE AR.REQUESTKEY = RA.REQUESTKEY
and AR.REQUESTOR=u.USERKEY
and RA.userkey=m.userkey
and AR.STATUS=3
AND AR.COMPLETEDATE >= DATE_SUB(NOW(), INTERVAL 1 hour);

Thanks

GK

 

11 REPLIES 11

Raghu
Honored Contributor
Honored Contributor

@geet14  use below query for for completion of task based on creation date or update date with task status=3

 

SELECT distinct a.UPDATEDATE,a.STARTDATE,a.ENDDATE,u.username,u.email,(SELECT DISTINCT SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid))) AS 'Request ID' , AR.ENDPOINTASCSV AS 'APPLICATION',m.userkey as requestee,m.firstname as requesteefirstname,m.email as requesteeemail,AR.REQUESTDATE as 'Request Date',AR.status as requeststatus,AR.COMPLETEDATE as requestcompleteddate
FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA, users u,users m,arstasks a
WHERE AR.REQUESTKEY = RA.REQUESTKEY
and AR.REQUESTOR=u.USERKEY
and RA.userkey=m.userkey
and a.REQUESTKEY=AR.REQUESTKEY
and AR.STATUS=3 and a.STARTDATE >DATE_SUB(NOW(), INTERVAL 24 HOUR)


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

geet14
New Contributor III
New Contributor III

Thank you . But is there way we can use AR.COMPLETEDATE FROM ARS_REQUESTS AR table.

 

 

Completedate seems to be null.

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

rushikeshvartak
All-Star
All-Star

Request completion date is not populated in ars request Please use below query 

SELECT u.username,
       u.email,
       Substr(AR.jbpmprocessinstanceid, Instr(AR.jbpmprocessinstanceid, '.') + 1
       ) AS
       'Request ID',
       AR.endpointascsv
       AS 'APPLICATION',
       m.userkey
       AS requestee,
       m.firstname
       AS requesteefirstname,
       m.email
       AS requesteeemail,
       AR.requestdate
       AS 'Request Date',
       AR.status
       AS requeststatus,
       Max(Ifnull(AA.approvedate, AA.submitdate))
       AS requestcompleteddate
FROM   ars_requests AR
       inner join request_access RA
               ON AR.requestkey = RA.requestkey
       inner join access_approvers AA
               ON RA.requestkey = AA.request_access_key
       inner join users u
               ON AR.requestor = u.userkey
       inner join users m
               ON RA.userkey = m.userkey
WHERE  AR.status = 3
GROUP  BY AR.requestkey
HAVING Max(Ifnull(AA.approvedate, AA.submitdate)) >= Date_sub(Now(),
                                                     interval 1 hour); 

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Thank you , but data is not found. When I removed last condition data is coming but requirement is to fetch last 1 hour request completion. Note, I was trying with different hours.

geet14_0-1717062306991.png

 

Compare data as per your data. i have validated query with my sample data and it works


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Thank you for reply.

FYI

I submitted the request,approved,completed tasks  and executed the query data not found.

Note : I tried to check with different hours still not data found like for 1 hour, 20 hour etc..

HAVING Max(Ifnull(AA.approvedate, AA.submitdate)) >= Date_sub(Now(),
                                                     interval 1 hour); 

Thanks

GK

HAVING Max(Ifnull(AA.approvedate, AA.submitdate)) >= Date_sub(Now(),
                                                     interval 1 hour); 

geet14
New Contributor III
New Contributor III

Tried without ifnull() condition also, still no data found.

geet14_0-1717098553161.png

 

What if you remove conditions? Also print timediff in select and fix logic as per your requirements 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

HI,

I have modified the join condition and data is coming but all tasks are not completed. Expected result is when all tasks completed then notification should triggered. I am trying to find from request page status.

query :

SELECT u.username,
u.email,
Substr(AR.jbpmprocessinstanceid, Instr(AR.jbpmprocessinstanceid, '.') + 1
) AS
'Request ID',
AR.endpointascsv
AS 'APPLICATION',
m.userkey
AS requestee,
m.firstname
AS requesteefirstname,
m.email
AS requesteeemail,
AR.requestdate
AS 'Request Date',
AR.status
AS requeststatus,
Max(Ifnull(AA.approvedate, AA.submitdate))
AS requestcompleteddate
FROM ars_requests AR
inner join request_access RA
ON AR.requestkey = RA.requestkey
inner join access_approvers AA
ON RA.REQUEST_ACCESSKEY = AA.request_access_key
inner join users u
ON AR.requestor = u.userkey
inner join users m
ON RA.userkey = m.userkey
WHERE AR.status = 3
GROUP BY AR.requestkey
HAVING Max(Ifnull(AA.approvedate, AA.submitdate)) >= Date_sub(Now(),
interval 30 hour);

Screen shot some of the tasks are still in process but still it's coming in report:

geet14_0-1717594875463.png

 

 

Report is not printing any task information. can you elaborate issue which screenshot from data analyzer vs request history


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.