Send notification for last 1 hour completed Requests

[geet14]

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

[Raghu]

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

[geet14]

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

[dgandhi]

Completedate seems to be null.

[rushikeshvartak]

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

[geet14]

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.

[rushikeshvartak]

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

[geet14]

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]

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

[rushikeshvartak]

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

[geet14]

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:

[rushikeshvartak]

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