MySQL Analyses related to processes

1 Process-Related
- 1.1 Instance-Related
- 1.2 Definitions-Related
  - 1.2.1 Number of loops (>0) per definition

Process-Related

This article offers examples for instance and definition related analyses with the dashboard.

Instance-Related

Number of started instances per month

SELECT
CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) AS Month,
  SUM(1) AS COUNT
FROM view_instance inst
  GROUP BY CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m'))
  ORDER BY CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) DESC;

Average number of started instances per day per month (22 working days a month)

SELECT
CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) AS Month,
ROUND(SUM(1)/22,1) AS "Average per day"
FROM view_instance inst
GROUP BY CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m'))
ORDER BY CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) DESC;

Number of started and completed instances for each definition

SELECT
inst1.definitionName AS "Process",
COUNT(DISTINCT(inst1.id)) AS "Number of started instances",
COUNT(DISTINCT(inst2.id)) AS "Number of completed instances"
FROM view_instance inst1
LEFT JOIN view_instance inst2
ON inst1.id=inst2.id
AND inst2.INSTANCEEND IS NOT NULL
INNER JOIN view_activity act ON act.instanceId=inst1.id
GROUP BY inst1.definitionName;

Number of started, completed, aborted and running instances for each process definition

SELECT
definitionName AS "Definition name",
SUM(1) AS started,
SUM(CASE WHEN isArchiv= '0' And instanceEnd is NULL THEN 1 ELSE 0 end) AS running,
SUM(CASE WHEN isArchiv= '1' And instanceEnd is NULL THEN 1 ELSE 0 END) AS aborted,
SUM(CASE WHEN instanceEnd is NOT NULL THEN 1 ELSE 0 end) AS completed
FROM view_instance GROUP BY definitionName;

Activities, tasks and assigned users of running instances

SELECT
inst.INSTANCENAME AS "Instance",
inst.DEFINITIONNAME AS "Process",
act.ACTIVITYNAME AS "Activity",
task.TASKNAME AS "Tasks",
CASE WHEN ident.ID IS NULL THEN ident2.IDENTITYNAME ELSE CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(ident.FIRSTNAME, ' '), ident.LASTNAME),' ('), ident2.IDENTITYNAME),')') END AS "Username"
FROM view_instance inst
INNER JOIN view_activity act
ON act.INSTANCEID = inst.ID
AND act.ACTIVITYSTART IS NOT NULL
AND act.ACTIVITYEND IS NULL AND act.ACTIVITYTYPE = 'K'
INNER JOIN view_task task
ON task.ACTIVITYID = act.ID
LEFT JOIN view_identity ident
ON task.ACTORID = ident.ID
INNER JOIN view_identity ident2
ON task.POOLEDACTORID = ident2.ID
WHERE inst.ISARCHIV = '0'
AND inst.INSTANCEEND IS NULL
GROUP BY inst.ID, inst.INSTANCENAME, inst.DEFINITIONNAME
ORDER BY inst.DEFINITIONNAME, inst.INSTANCENAME;

Average cycle time (in h) of all instances of all process definitions per month

SELECT
  CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) AS Month,
  IF((UNIX_TIMESTAMP(instanceEnd ) - UNIX_TIMESTAMP(instanceStart))>0, ROUND((AVG(UNIX_TIMESTAMP(instanceEnd) - UNIX_TIMESTAMP(INSTANCESTART)))/3600), '0')  AS "CYCLE TIME"
FROM view_instance
WHERE instanceEnd IS NOT NULL
  GROUP BY CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m'))
  ORDER BY CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) DESC;

Number of started instances per month (columns) per year (rows) for a definition

SELECT
Year as "Year",
SUM(CASE WHEN Month LIKE '01' THEN 1 ELSE 0 END) AS "January",
SUM(CASE WHEN Month LIKE '02'THEN 1 ELSE 0 END) AS "February",
SUM(CASE WHEN Month LIKE '03' THEN 1 ELSE 0 END) AS "March",
SUM(CASE WHEN Month LIKE '04' THEN 1 ELSE 0 END) AS "April",
SUM(CASE WHEN Month LIKE '05' THEN 1 ELSE 0 END) AS "May",
SUM(CASE WHEN Month LIKE '06' THEN 1 ELSE 0 END) AS "June",
SUM(CASE WHEN Month LIKE '07' THEN 1 ELSE 0 END) AS "July",
SUM(CASE WHEN Month LIKE '08' THEN 1 ELSE 0 END) AS "August",
SUM(CASE WHEN Month LIKE '09' THEN 1 ELSE 0 END) AS "September",
SUM(CASE WHEN Month LIKE '10' THEN 1 ELSE 0 END) AS "October",
SUM(CASE WHEN Month LIKE '11' THEN 1 ELSE 0 END) AS "November",
SUM(CASE WHEN Month LIKE '12' THEN 1 ELSE 0 END) AS "December"
FROM
(SELECT
DATE_FORMAT(creationTime, '%m') AS Month,
DATE_FORMAT(creationTime, '%Y') AS Year
FROM view_instance
WHERE definitionName = '<name_of_process_definition>'
) AS subqu
GROUP BY Year
ORDER BY Year;

Cumulative number of started instances per month (columns) per year (rows) for a definition

SELECT
Year as "Year",
SUM(CASE WHEN Month LIKE '01' THEN 1 ELSE 0 END) AS "January",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' THEN 1 ELSE 0 END) AS "February",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' THEN 1 ELSE 0 END) AS "March",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04' THEN 1 ELSE 0 END) AS "April",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04' OR Month LIKE '05' THEN 1 ELSE 0 END) AS "May",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' THEN 1 ELSE 0 END) AS "June",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' OR Month LIKE '07' THEN 1 ELSE 0 END) AS "July",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' OR Month LIKE '07' OR Month LIKE '08' THEN 1 ELSE 0 END) AS "August",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' OR Month LIKE '07' OR Month LIKE '08' OR Month LIKE '09' THEN 1 ELSE 0 END) AS "September",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' OR Month LIKE '07' OR Month LIKE '08' OR Month LIKE '09' OR Month LIKE '10' THEN 1 ELSE 0 END) AS "October",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' OR Month LIKE '07' OR Month LIKE '08' OR Month LIKE '09' OR Month LIKE '10' OR Month LIKE '11' THEN 1 ELSE 0 END) AS "November",
SUM(CASE WHEN Month LIKE '01' OR Month LIKE '02' OR Month LIKE '03' OR Month LIKE '04'OR Month LIKE '05' OR Month LIKE '06' OR Month LIKE '07' OR Month LIKE '08' OR Month LIKE '09' OR Month LIKE '10' OR Month LIKE '11' OR Month LIKE '12' THEN 1 ELSE 0 END) AS "December"FROM
(SELECT
DATE_FORMAT(creationTime, '%m') AS Month,
DATE_FORMAT(creationTime, '%Y') AS Year
FROM view_instance
WHERE definitionName = '<name_of_process_definition>'
) AS subqu
GROUP BY Year
ORDER BY Year;

Minimum, maximum and average cycle time (in h) of instances per process definition

SELECT
  CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) AS Month,
  IF((UNIX_TIMESTAMP(instanceEnd ) - UNIX_TIMESTAMP(instanceStart))>0, ROUND((AVG(UNIX_TIMESTAMP(instanceEnd) - UNIX_TIMESTAMP(INSTANCESTART)))/3600), '0')  AS "Average cycle time",
  IF((UNIX_TIMESTAMP(instanceEnd ) - UNIX_TIMESTAMP(instanceStart))>0, ROUND((MIN(UNIX_TIMESTAMP(instanceEnd) - UNIX_TIMESTAMP(INSTANCESTART)))/3600), '0')  AS "Min cycle time",
  IF((UNIX_TIMESTAMP(instanceEnd ) - UNIX_TIMESTAMP(instanceStart))>0, ROUND((MAX(UNIX_TIMESTAMP(instanceEnd) - UNIX_TIMESTAMP(INSTANCESTART)))/3600), '0')  AS "Max cycle time"
FROM view_instance
WHERE instanceEnd IS NOT NULL
  GROUP BY CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m'))
  ORDER BY CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) DESC;

Definitions-Related

Number of loops (>0) per definition

SELECT
defName AS Process,
SUM(lCount) AS NumberOfLoops
FROM
(
SELECT
  inst.definitionName AS "defName",
  inst.instanceName AS "instName",
  act.loopCount AS "lCount",
  inst.id AS "piId",
  act.activityName AS "Activity Name"
FROM view_instance inst
LEFT JOIN view_activity act
  ON act.instanceId=inst.id
WHERE act.loopCount IS NOT NULL
GROUP BY inst.id, inst.definitionName, inst.instanceName, act.loopCount, act.activityName) AS subqu
GROUP BY defName;