MySQL Analyses related to processes

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

 

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

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

 

Activities, tasks and assigned users of running instances

 

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

 

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

 

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

 

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

 

Number of loops (>0) per definition

 

© TIM Solutions GmbH | AGB | Datenschutz | Impressum