Plugin installed incorrectly. Rename plugin directory 'swiftmail.backup' to 'swiftmail'.
software:dashboard:analyses:activity_analyses
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
|
software:dashboard:analyses:activity_analyses [2014/12/16 16:31] 127.0.0.1 Externe Bearbeitung |
software:dashboard:analyses:activity_analyses [2021/07/01 09:52] |
||
|---|---|---|---|
| Zeile 1: | Zeile 1: | ||
| - | =====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") | ||
| - | GROUP BY activity.name | ||
| - | ) AS datediffsub | ||
| - | WHERE datediff > 0 | ||
| - | AND defName = "Name der Prozessdefinition" | ||
| - | 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> | ||
software/dashboard/analyses/activity_analyses.txt · Zuletzt geändert: 2021/07/01 09:52 (Externe Bearbeitung)
Falls nicht anders bezeichnet, ist der Inhalt dieses Wikis unter der folgenden Lizenz veröffentlicht: 2013 T!M Solutions GmbH
