MySQL Analyses related to tasks and activities

Owned by Betül Kara (Unlicensed)
Last updated: Mar 08, 2023

This article offers examples for the 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 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>'

 

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;

 

© TIM Solutions GmbH | AGB | Datenschutz | Impressum