MySQL Analyses related to tasks and activities

1 Task and Activity-Related
- 1.1 Task-Related
  - 1.1.1 All open tasks for a specified user
  - 1.1.2 Number of created, completed, and open tasks for each process definition
- 1.2 Activity-Related
  - 1.2.1 Average, minimum and maximum cycle time of all activities of a specific process definition (in h) per month

Task and Activity-Related

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

Task-Related

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
   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>'

Activity-Related

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

SELECT
    CONCAT(DATE_FORMAT(inst.creationTime, '%Y'), '-', DATE_FORMAT(inst.creationTime, '%m')) AS Month,
    act.activityName AS "Activity",
    IF((UNIX_TIMESTAMP(act.activityEnd ) - UNIX_TIMESTAMP(act.activityStart))>0, ROUND((AVG(UNIX_TIMESTAMP(act.activityEnd) - UNIX_TIMESTAMP(act.activityStart)))/3600), '0')  AS "Average cycle time",
  IF((UNIX_TIMESTAMP(act.activityEnd ) - UNIX_TIMESTAMP(act.activityStart))>0, ROUND((MIN(UNIX_TIMESTAMP(act.activityEnd) - UNIX_TIMESTAMP(act.activityStart)))/3600), '0')  AS "Min cycle time",
  IF((UNIX_TIMESTAMP(act.activityEnd ) - UNIX_TIMESTAMP(act.activityStart))>0, ROUND((MAX(UNIX_TIMESTAMP(act.activityEnd) - UNIX_TIMESTAMP(act.activityStart)))/3600), '0')  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(DATE_FORMAT(inst.creationTime, '%Y'), '-', DATE_FORMAT(inst.creationTime, '%m')), act.activityName
  ORDER BY CONCAT(DATE_FORMAT(inst.creationTime, '%Y'), '-', DATE_FORMAT(inst.creationTime, '%m')) DESC;