Need Query for KPI

[Dev]

I need to build a KPI´s

-- Time spent in the first Approval by level 1 (Manager)
*Avg time
*Count of requests exceeding 5 days in rolling 30 day period
-- Time spent in 2nd approval Role Owner
*Avg time
*Count of requests exceeding 5 days in rolling 30 day period
-- Time spent provisioning between 2nd approval and request competition
*Avg time
*Count of requests exceeding 5 days rolling 30 day period

[Dhruv_S]

Please refer to Database schema reference for relevant tables like access_approvers, ars_requests, request_access etc.

Database Schema Reference (saviyntcloud.com)

Regards,

Dhruv Sharma

[rushikeshvartak · Accepted solution]

Hi @Dev 

First 2 Query 

SELECT
'Avg Time spent in the first Approval by level 1 (Manager)' as category ,

(SUM(TIMESTAMPDIFF(HOUR, submitdate, approvedate)) / COUNT(username)) AS 'Data' FROM ARS_REQUESTS ar, ACCESS_APPROVERS aa, users u, (SELECT r.REQUEST_ACCESSKEY, r.ACCESSKEY, r.ACCESSTYPE, r.ENDDATE, CASE WHEN r.PARENTREQUEST IS NULL THEN r.REQUEST_ACCESSKEY ELSE r.PARENTREQUEST END 'PARENTREQUEST', r.REQUESTKEY, r.REQUESTTYPE, r.STARTDATE, r.USERKEY, r.STATUS FROM REQUEST_ACCESS r) ra, request_access_attrs raa, endpoints en WHERE ar.REQUESTKEY = ra.REQUESTKEY AND raa.request_access_key = ra.PARENTREQUEST AND raa.attribute_value = en.endpointkey AND raa.attribute_name = 'ENDPOINT' AND ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY AND 1 = 1 AND ar.status NOT IN (4, 6) AND u.userkey = aa.approveby
and JBPM_ACTIVITY_NAME='Manager' and en.DISPLAYNAME ='Rushi' AND ar.requestdate BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
GROUP BY en.DISPLAYNAME
UNION
SELECT
'Count of requests exceeding 5 days in rolling 30 day period by level 1 (Manager)' as category ,
count(*) AS 'Data' FROM ARS_REQUESTS ar, ACCESS_APPROVERS aa, users u, (SELECT r.REQUEST_ACCESSKEY, r.ACCESSKEY, r.ACCESSTYPE, r.ENDDATE, CASE WHEN r.PARENTREQUEST IS NULL THEN r.REQUEST_ACCESSKEY ELSE r.PARENTREQUEST END 'PARENTREQUEST', r.REQUESTKEY, r.REQUESTTYPE, r.STARTDATE, r.USERKEY, r.STATUS FROM REQUEST_ACCESS r) ra, request_access_attrs raa, endpoints en WHERE ar.REQUESTKEY = ra.REQUESTKEY AND raa.request_access_key = ra.PARENTREQUEST AND raa.attribute_value = en.endpointkey AND raa.attribute_name = 'ENDPOINT' AND ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY AND 1 = 1 AND ar.status NOT IN (4, 6) AND u.userkey = aa.approveby
and JBPM_ACTIVITY_NAME='Manager' and en.DISPLAYNAME ='Rushi' AND ar.requestdate BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
AND TIMESTAMPDIFF(DAY, aa.submitdate, aa.approvedate) > 5
GROUP BY en.DISPLAYNAME