Oracle Analyses related to tasks and activities

Owned by Betül Kara (Unlicensed)
Last updated: Mar 09, 2023
2 min read

Task and Activity-related

This article offers examples for task and activity related analyses with the dashboard.

All open tasks for a specified user

SELECT    ident.LASTNAME AS "Lastname",    ident.FIRSTNAME AS "Firstsname",    ident.IDENTITYNAME AS "Username",    task.TASKNAME AS "Task",    inst.INSTANCENAME AS "Instance name",    inst.DEFINITIONNAME AS "Processname" FROM view_activity act, view_task task, view_identity ident, view_instance inst WHERE act.ID = task.ACTIVITYID    AND task.ACTORID = ident.ID    AND act.ACTIVITYEND IS NULL    AND task.TASKEND IS NULL    AND inst.ID = act.INSTANCEID    AND inst.ISARCHIV = '0'    AND inst.INSTANCEEND IS NULL    AND ident.FIRSTNAME = '<specific_user_firstname>'    AND ident.LASTNAME = '<specific_user_lastname>'

 

Number of created, completed, and open tasks for each process definition

SELECT inst.DEFINITIONNAME AS "Definition name", SUM(1) AS "Number of created tasks", Sum(CASE WHEN task.TASKEND IS NOT NULL THEN 1 ELSE 0 END) AS "Number of completed tasks", SUM(CASE WHEN task.TASKEND is NULL AND task.ISARCHIV = 'false' THEN 1 ELSE 0 END) AS "Number of open tasks" FROM view_task task INNER JOIN view_instance inst ON task.INSTANCEID=inst.ID GROUP BY inst.DEFINITIONNAME

Average, minimum and maximum cycle time of all activities of a specific process definition (in h) per month

SELECT CONCAT(CONCAT(to_char(inst.CREATIONTIME,'yyyy'), '-'), to_char(inst.CREATIONTIME,'mm')) AS Month, act.activityName AS "Activity", ROUND(AVG(24*ROUND(to_number(to_date(to_char(act.activityEnd, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date(to_char(act.activityStart, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi')),2))) AS "Average cycle time", ROUND(MIN(24*ROUND(to_number(to_date(to_char(act.activityEnd, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date(to_char(act.activityStart, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi')),2))) AS "Min cycle time", ROUND(MAX(24*ROUND(to_number(to_date(to_char(act.activityEnd, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi') - to_date(to_char(act.activityStart, 'YYYY-MM-DD hh24:mi'), 'YYYY-MM-DD hh24:mi')),2))) AS "Max cycle time" FROM view_activity act LEFT JOIN view_instance inst ON act.instanceId=inst.id WHERE act.activityEnd IS NOT NULL AND act.activityStart IS NOT NULL AND act.activityType IN ('K','S') AND inst.definitionName = '<name_of_process_definition>' GROUP BY CONCAT(CONCAT(to_char(inst.CREATIONTIME,'yyyy'), '-'), to_char(inst.CREATIONTIME,'mm')), act.activityName ORDER BY CONCAT(CONCAT(to_char(inst.CREATIONTIME,'yyyy'), '-'), to_char(inst.CREATIONTIME,'mm'));

 

© TIM Solutions GmbH | AGB | Datenschutz | Impressum