Table of Contents

FIXME This page is not fully translated, yet. Please help completing the translation.
(remove this paragraph once the translation is finished)

Number of created, completed, and open activities for each process definition

SELECT inst.definitionname AS Prozess,
COUNT(DISTINCT(act1.id)) AS "Number of created activities",
COUNT(DISTINCT(act2.id)) AS "Number of finished activities",
COUNT(DISTINCT(act3.id)) AS "Number of open activities"
FROM view_activity act1 INNER JOIN view_instance inst ON act1.instanceid=inst.id AND act1.START IS NOT NULL AND TYPE IN ("K","S")
LEFT JOIN view_activity act2 ON act1.id=act2.id AND act2.END IS NOT NULL
LEFT JOIN view_activity act3 ON act1.id=act3.id AND act3.end IS NULL AND act3.start IS NOT NULL
GROUP BY Prozess

Number of created, completed, and open activities for a specified process definition

SELECT defName AS Prozess,
COUNT(ended) AS "Number of created activities",
SUM(ended) AS "Number of finished activities", (SELECT COUNT(DISTINCT(task.id))
FROM view_task task INNER JOIN view_instance inst ON task.instanceid = inst.id
INNER JOIN view_activity act ON act.instanceid = inst.id
WHERE task.isopen IS TRUE AND act.END IS NULL AND inst.END IS NULL AND inst.archiv IS FALSE
AND inst.definitionname = defName) AS "Number of open activities" FROM
(SELECT inst.definitionname AS defname, IF(act.END IS NULL,0,1) AS ended FROM view_activity act
INNER JOIN view_instance inst ON inst.id=act.instanceid
WHERE TYPE IN ("K","S") AND START IS NOT NULL AND inst.definitionname= "Name of process definition") AS subQu

Average time (in hh:mm:ss) from creation to completion for all activities (regardless of process definition and including time outside of working hours)

SELECT IF((FLOOR(sekunden))<60,
  concat(IF((FLOOR(sekunden))<10,"00:00:0","00:00:"),(FLOOR(sekunden))),
  IF((FLOOR(sekunden))>3600,
    CAST(concat(h,IF(FLOOR((FLOOR(sekunden)- h*3600-sek)/60)<10,":0",":"),FLOOR((FLOOR(sekunden)- h*3600-sek)/60),IF(sek<10,":0",":"),sek) AS CHAR),
    concat(IF(FLOOR(sekunden/60)<10,"00:0","00:"),FLOOR(sekunden/60),IF(FLOOR(sek)<10,":0",":"),FLOOR(sek))
  )
) AS "Average wait time for activities" FROM (SELECT FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))) AS sekunden,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))%60) AS sek,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))/3600) AS h FROM view_activity 
WHERE START IS NOT NULL AND END IS NOT NULL AND TYPE IN ("K","S")) AS dusub

Average time (in hh:mm:ss) from creation to completion for all activities per month (regardless of process definition and including time outside of working hours)

SELECT Monat, IF((FLOOR(sekunden))<60,
  concat(IF((FLOOR(sekunden))<10,"00:00:0","00:00:"),(FLOOR(sekunden))),
  IF((FLOOR(sekunden))>3600,
    CAST(concat(h,IF(FLOOR((FLOOR(sekunden)- h*3600-sek)/60)<10,":0",":"),FLOOR((FLOOR(sekunden)- h*3600-sek)/60),IF(sek<10,":0",":"),sek) AS CHAR),
    concat(IF(FLOOR(sekunden/60)<10,"00:0","00:"),FLOOR(sekunden/60),IF(FLOOR(sek)<10,":0",":"),FLOOR(sek))
  )
) AS "Average wait time for activities" FROM
(SELECT concat(SUBSTRING(monthname(START),1,3)," ",SUBSTRING(CAST(YEAR(START) AS CHAR),3,2)) AS Monat,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))) AS sekunden,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))%60) AS sek,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))/3600) AS h FROM view_activity WHERE END IS NOT NULL
  AND START IS NOT NULL AND TYPE IN ("K","S") GROUP BY Monat ORDER BY YEAR(START) DESC, MONTH(START) DESC) AS dusub

Average time (in hh:mm:ss) from creation to completion for all activities, excluding outliers (regardless of process definition and including time outside of working hours)

SELECT Monat, IF((FLOOR(sekunden))<60,
  concat(IF((FLOOR(sekunden))<10,"00:00:0","00:00:"),(FLOOR(sekunden))),
  IF((FLOOR(sekunden))>3600,
    CAST(concat(h,IF(FLOOR((FLOOR(sekunden)- h*3600-sek)/60)<10,":0",":"),FLOOR((FLOOR(sekunden)- h*3600-sek)/60),IF(sek<10,":0",":"),sek) AS CHAR),
    concat(IF(FLOOR(sekunden/60)<10,"00:0","00:"),FLOOR(sekunden/60),IF(FLOOR(sek)<10,":0",":"),FLOOR(sek))
  )
) AS "Average wait time for activities" FROM
(SELECT concat(SUBSTRING(monthname(START),1,3)," ",SUBSTRING(CAST(YEAR(START) AS CHAR),3,2)) AS Monat,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))) AS sekunden,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))%60) AS sek,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))/3600) AS h FROM view_activity
WHERE START IS NOT NULL AND END IS NOT NULL AND TYPE IN ("K","S") AND CAST(FLOOR((unix_timestamp(END) - unix_timestamp(START))) AS DECIMAL(10,10)) < 10000 GROUP BY YEAR(START) DESC, MONTH(START) DESC) AS dusub

Average, minimum, and maximum lead time of all activities within a specified process definition (in hh:mm:ss)

SELECT  name AS Aktivität,
IF((FLOOR(avgSekunden))<60,
  concat(IF((FLOOR(avgSekunden))<10,"00:00:0","00:00:"),(FLOOR(avgSekunden))),
  IF((FLOOR(avgSekunden))>3600,
    CAST(concat(avgH,IF(FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60)<10,":0",":"),FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60),IF(avgSek<10,":0",":"),avgSek) AS CHAR),
    concat(IF(FLOOR(avgSekunden/60)<10,"00:0","00:"),FLOOR(avgSekunden/60),IF(FLOOR(avgSek)<10,":0",":"),FLOOR(avgSek))
  )
)AS "Average lead time",
IF((FLOOR(minSekunden))<60,
  concat(IF((FLOOR(minSekunden))<10,"00:00:0","00:00:"),(FLOOR(minSekunden))),
  IF((FLOOR(minSekunden))>3600,
    CAST(concat(minH,IF(FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60)<10,":0",":"),FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60),IF(minSek<10,":0",":"),minSek) AS CHAR),
    concat(IF(FLOOR(minSekunden/60)<10,"00:0","00:"),FLOOR(minSekunden/60),IF(FLOOR(minSek)<10,":0",":"),FLOOR(minSek))
  )
) AS "Minimum lead time",
IF((FLOOR(maxSekunden))<60,
  concat(IF((FLOOR(maxSekunden))<10,"00:00:0","00:00:"),(FLOOR(maxSekunden))),
  IF((FLOOR(maxSekunden))>3600,
    CAST(concat(maxH,IF(FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60)<10,":0",":"),FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60),IF(maxSek<10,":0",":"),maxSek) AS CHAR),
    concat(IF(FLOOR(maxSekunden/60)<10,"00:0","00:"),FLOOR(maxSekunden/60),IF(FLOOR(maxSek)<10,":0",":"),FLOOR(maxSek))
  )
) AS "Maximum lead time"
 
FROM
(SELECT activity.name AS name, definitionname AS defName,
FLOOR(MIN((unix_timestamp(activity.END) - unix_timestamp(activity.START)))) AS minSekunden,
FLOOR(MIN((unix_timestamp(activity.END) - unix_timestamp(activity.START)))%60) AS minSek,
FLOOR(MIN((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS minH,
FLOOR(MAX((unix_timestamp(activity.END) - unix_timestamp(activity.START)))) AS maxSekunden,
FLOOR(MAX((unix_timestamp(activity.END) - unix_timestamp(activity.START)))%60) AS maxSek,
FLOOR(MAX((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS maxH,
FLOOR(avg((unix_timestamp(activity.END) - unix_timestamp(activity.START)))) AS avgSekunden,
FLOOR(avg((unix_timestamp(activity.END) - unix_timestamp(activity.START)))%60) AS avgSek,
FLOOR(avg((unix_timestamp(activity.END) - unix_timestamp(activity.START)))/3600) AS avgH,
(unix_timestamp(activity.END)-unix_timestamp(activity.START)) AS datediff
FROM view_activity activity INNER JOIN view_instance inst ON activity.instanceId=inst.id
WHERE activity.END IS NOT NULL AND activity.START IS NOT NULL AND activity.TYPE IN ("K","S") AND definitionname = "Name of process definition"
GROUP BY activity.name
) AS datediffsub
WHERE datediff > 0
GROUP BY name

Average, minimum, and maximum lead time of all activities within a specified process definition (in h)

SELECT  activity.name AS Aktivität,
CAST((MIN((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS DECIMAL(10,1)) AS "Minimum lead time",
CAST((AVG((unix_timestamp(activity.END) - unix_timestamp(activity.START)))/3600) AS DECIMAL(10,1)) AS "Average lead time",
CAST((MAX((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS DECIMAL(10,1)) AS "Maximum lead time"
FROM
view_activity activity INNER JOIN view_instance inst ON activity.instanceId=inst.id AND activity.TYPE IN ("K","S") AND
activity.END IS NOT NULL AND activity.START IS NOT NULL WHERE (unix_timestamp(activity.END)-unix_timestamp(activity.START)) > 0
AND definitionname = "Name of process definition"
GROUP BY activity.name

Average, minimum, and maximum lead time of all activities within a specified process definition (in h) per month

SELECT concat(SUBSTRING(monthname(activity.START),1,3)," ",SUBSTRING(CAST(YEAR(activity.START) AS CHAR),3,2)) AS Monat, activity.name AS Aktivität,
CAST((MIN((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS DECIMAL(10,1)) AS "Minimum lead time",
CAST((AVG((unix_timestamp(activity.END) - unix_timestamp(activity.START)))/3600) AS DECIMAL(10,1)) AS "Average lead time",
CAST((MAX((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS DECIMAL(10,1)) AS "Maximum lead time"
FROM
view_activity activity INNER JOIN view_instance inst ON activity.instanceId=inst.id AND activity.TYPE IN ("K","S") AND
activity.END IS NOT NULL AND activity.START IS NOT NULL WHERE (unix_timestamp(activity.END)-unix_timestamp(activity.START)) > 0
AND definitionname = "Name of process definition"
GROUP BY YEAR(activity.START), MONTH(activity.START) DESC, activity.name