Information needed on requests

[mohankota21]

Hi Team,

We want to create an analytics query which will detect the requests that are -

- approved but tasks are not created
- tasks are completed but the request is not closed.

Let us know the query that can be used in analytics query for the same. Also, the relevant table names which has this data.

[rushikeshvartak]

Hung Request

ELECT DISTINCT (SELECT DISTINCT SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) ) AS 'REQUESTID', r.REQUESTKEY,r.REQUESTDATE, SUBSTR(aa.APPROVEDATE, 1, 10) AS `APPROVAL DATE`, j.JBPMEXCEPTION, j.STATUS AS JBPMSTATUS FROM jbpmretry j, ars_requests r, request_access ra, access_approvers aa WHERE j.ACCESS_APPROVERKEY = aa.ACCESS_APPROVERSKEY AND ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY AND ra.REQUESTKEY = r.REQUESTKEY AND r.status NOT IN (4 , 3, 6) AND aa.APPROVEDATE > DATE_SUB(NOW(), INTERVAL 30 DAY) 

[rushikeshvartak]

Approved Request with no tasks

select distinct '' as REQUESTID, ars.REQUESTKEY,'' as REQUESTDATE, a.APPROVEDATE from arstasks ars join ( select distinct ar.requestkey,max(aa.approvedate) as approvedate from ars_requests ar join request_access ra on ra.requestkey = ar.requestkey join access_approvers aa on aa.request_access_key = ra.request_accesskey where ar.REQUESTDATE > NOW() - INTERVAL 1 day and ar.status = 3 group by 1) as a on a.requestkey = ars.requestkey where ars.TASKDATE > a.approvedate + INTERVAL 20 minute and ars.STARTDATE < a.approvedate  UNION select DISTINCT SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) REQUESTID,ar.REQUESTKEY, ar.REQUESTDATE,SUBSTR(aa.APPROVEDATE, 1, 10) AS `APPROVAL DATE` from ars_requests ar join request_access ra on ar.requestkey = ra.requestkey join access_approvers aa on aa.request_access_key = ra.request_accesskey left join arstasks att on att.requestaccesskey =ra.request_accesskey where ar.status in (1,2) and ra.status =1  and att.REQUESTACCESSKEY is null and ar.requestkey not in (select a.requestkey from ars_requests a, request_access b, access_approvers c where a.requestkey = b.requestkey and b.REQUEST_ACCESSKEY = c.REQUEST_ACCESS_KEY and c.STATUS = 1)