...
Number of started instances per month
Code Block |
---|
|
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)
Code Block |
---|
|
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
Code Block |
---|
|
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
Code Block |
---|
|
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
Code Block |
---|
|
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
Code Block |
---|
|
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
Code Block |
---|
|
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
...
Minimum, maximum and average cycle time (in h) of instances per process definition
Code Block |
---|
|
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
Code Block |
---|
|
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; |