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

=====Activity-Related Analysis=====

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

<code sql>
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
</code>
==== Number of created, completed, and open activities for a specified process definition ====

<code sql>
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
</code>
==== Average time (in hh:mm:ss) from creation to completion for all activities (regardless of process definition and including time outside of working hours) ====

<code sql>
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
</code>

==== 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) ====

<code sql>
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
</code>

==== 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)  ====

<code sql>
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  
</code>

==== Average, minimum, and maximum lead time of all activities within a specified process definition (in hh:mm:ss) ====
<code sql>
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
</code>

==== Average, minimum, and maximum lead time of all activities within a specified process definition (in h) ====
<code sql>
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
</code>

==== Average, minimum, and maximum lead time of all activities within a specified process definition (in h) per month====
<code sql>
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
</code>