=====Aktivitätenbezogene Auswertungen=====

==== Anzahl an erstellten, beendeten und offenen Aktivitäten je Prozessdefinition ====

<code sql>
SELECT inst.definitionname AS Prozess,
COUNT(DISTINCT(act1.id)) AS "Anzahl erstellte Aktivitäten",
COUNT(DISTINCT(act2.id)) AS "Anzahl beendete Aktivitäten",
COUNT(DISTINCT(act3.id)) as "Anzahl offene Aktivitäten"
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>
==== Anzahl an erstellten, beendeten und offenen Aktivitäten einer bestimmten Prozessdefinition ====

<code sql>
SELECT defName AS Prozess,
COUNT(ended) AS "Anzahl erstellte Aktivitäten",
SUM(ended) AS "Anzahl beendete Aktivitäten", (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 "Anzahl offene Aktivitäten" 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 der Prozessdefinition") AS subQu
</code>
==== Durchschnittliche Zeit (in hh:mm:ss) von Erstellung bis Erledigung von allen Aktivitäten (egal von welcher Prozessdefinition und auch außerhalb der Arbeitszeit) ====

<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 "Durchschnittliche Liegezeit von Aktivitäten" 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>

==== Durchschnittliche Zeit (in hh:mm:ss) von Erstellung bis Erledigung von allen Aktivitäten je Monat (egal von welcher Prozessdefinition und auch außerhalb der Arbeitszeit) je Monat ====

<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 "Durchschnittliche Liegezeit von Aktivitäten" 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>

==== Durchschnittliche Zeit (in hh:mm:ss) von Erstellung bis Erledigung von allen Aktivitäten je Monat ohne Ausreißer (von allen Prozessdefinitionen und auch außerhalb der Arbeitszeit) ====

<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 "Durchschnittliche Liegezeit von Aktivitäten" 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>

==== Durchschnittliche, minimale und maximale Durchlaufszeit aller Aktivitäten einer bestimmten Prozessdefinition (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 "Durchschnittliche Durchlaufzeit",
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 "Minimale Durchlaufzeit",
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 "Maximale Durchlaufzeit"
 
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 der Prozessdefinition"
GROUP BY activity.name
) AS datediffsub
WHERE datediff > 0
GROUP BY name
</code>

==== Durchschnittliche, minimale und maximale Durchlaufszeit aller Aktivitäten einer bestimmten Prozessdefinition (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 "Minimale Durchlaufzeit",
CAST((AVG((unix_timestamp(activity.END) - unix_timestamp(activity.START)))/3600) AS DECIMAL(10,1)) AS "Durchschnittliche Durchlaufzeit",
CAST((MAX((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS DECIMAL(10,1)) AS "Maximale Durchlaufzeit"
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 der Prozessdefinition"
GROUP BY activity.name
</code>

==== Durchschnittliche, minimale und maximale Durchlaufszeit aller Aktivitäten einer bestimmten Prozessdefinition (in h) je Monat ====
<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 "Minimale Durchlaufzeit",
CAST((AVG((unix_timestamp(activity.END) - unix_timestamp(activity.START)))/3600) AS DECIMAL(10,1)) AS "Durchschnittliche Durchlaufzeit",
CAST((MAX((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS DECIMAL(10,1)) AS "Maximale Durchlaufzeit"
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 der Prozessdefinition"
GROUP BY YEAR(activity.START), MONTH(activity.START) DESC, activity.name
</code>