Plugin installed incorrectly. Rename plugin directory 'swiftmail.backup' to 'swiftmail'.
software:dashboard:analyses
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
|
software:dashboard:analyses [2013/12/04 17:45] martin.oehm bezogene |
software:dashboard:analyses [2021/07/01 09:52] |
||
|---|---|---|---|
| Zeile 1: | Zeile 1: | ||
| - | =====Instanzbezogene Auswertungen===== | ||
| - | === Anzahl an gestarteten und beendeten Instanzen je Definition === | ||
| - | <code sql> | ||
| - | select inst1.definitionname as Prozess, count(distinct(inst1.id)) as "Anzahl gestarteter Instanzen", count(distinct(inst2.id)) as "Anzahl beendeter Instanzen", count(distinct(task.id)) as "Anzahl offener Aufgaben" | ||
| - | from view_instance inst1 left join view_instance inst2 on inst1.id=inst2.id and inst2.end is not null | ||
| - | inner join view_activity act on act.instanceid=inst1.id | ||
| - | left join view_task task on task.instanceid=inst1.id | ||
| - | and task.end is null and act.start is not null and act.end is null and inst1.end is null and inst1.archiv is false | ||
| - | group by inst1.definitionname | ||
| - | </code> | ||
| - | === Anzahl an gestarteten und beendeten Instanzen sowie die Anzahl an offenen Aufgaben einer Prozessdefinition === | ||
| - | <code sql> | ||
| - | select inst1.definitionname as Prozess, count(inst1.id) as "Anzahl gestarteter Instanzen", count(inst2.id) as "Anzahl beendeter Instanzen", (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 = Prozess) AS "Anzahl offene Aufgaben" | ||
| - | from view_instance inst1 left join view_instance inst2 on inst1.id=inst2.id and inst2.end is not null | ||
| - | where inst1.definitionname = "Name der Prozessdefinition" | ||
| - | </code> | ||
| - | |||
| - | === Anzahl an gestarteten, beendeten, abgebrochenen und laufenden Instanzen je Prozessdefinition === | ||
| - | <code sql> | ||
| - | select definitionname as Definitionsname, | ||
| - | sum(alle) as "Gestartete Instanzen", | ||
| - | sum(beendet) as "Beendete Instanzen (mit Ende)", | ||
| - | sum(abgebrochen) as "Abgebrochene Instanzen (Archiviert ohne Ende)", | ||
| - | sum(laufend) as "Laufende Instanzen" | ||
| - | from (Select if(archiv is true, | ||
| - | if(end is null,1,0),0) as abgebrochen, | ||
| - | if(end is not null,1,0) as beendet, | ||
| - | if(end is null,if(archiv is true,0,1),0) as laufend, | ||
| - | 1 as alle, definitionname | ||
| - | from view_instance) as subqu group by definitionname | ||
| - | </code> | ||
| - | |||
| - | === Finanzielle Aufwände je Instanz === | ||
| - | <code sql> | ||
| - | select inst.name as Instanzname,sum(eff.value) as Finanzaufwände | ||
| - | from view_effort eff | ||
| - | inner join view_instance inst on eff.instanceID=inst.id | ||
| - | where eff.effortType="FINANCE" | ||
| - | group by inst.id | ||
| - | </code> | ||
| - | |||
| - | === Anzahl der Schleifen (>0) je Instanz === | ||
| - | <code sql> | ||
| - | SELECT inst.name, act.loopcount FROM view_activity act | ||
| - | INNER JOIN view_instance inst ON inst.id=act.instanceID | ||
| - | WHERE act.loopcount IS NOT NULL | ||
| - | </code> | ||
| - | |||
| - | === Aktuelle Aktivität aller offenen Instanzen === | ||
| - | <code sql> | ||
| - | SELECT inst.name AS Prozessinstanz, group_concat(act.name SEPARATOR ', ') AS Aktivität | ||
| - | FROM view_instance inst | ||
| - | INNER JOIN view_activity act ON act.instanceid = inst.id AND act.START IS NOT NULL AND act.END IS NULL AND act.TYPE = 'K' | ||
| - | WHERE inst.archiv = FALSE AND inst.END IS NULL group by Prozessinstanz | ||
| - | </code> | ||
| - | |||
| - | ===Alle offenen Instanzen mit einer bestimmten Zeichenfolge im Instanznamen === | ||
| - | <code sql> | ||
| - | select distinct pi.name as Instanzname, pi.definitionName as Prozessname | ||
| - | from view_activity a, view_instance pi | ||
| - | where a.end is null and a.start is not null and pi.id = a.instanceId and pi.archiv = 0 and pi.end is null and pi.name like "%steffen%" | ||
| - | </code> | ||
| - | |||
| - | |||
| - | === Durchschnittliche Durchlaufszeit (in hh:mm:ss) von allen Instanzen aller Prozessdefinitionen 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 Durchlaufzeit" from | ||
| - | (SELECT FLOOR(avg(TIMESTAMPDIFF(SECOND,creationtime,END))) AS sekunden, | ||
| - | FLOOR(avg(TIMESTAMPDIFF(SECOND,creationtime,END))%60) AS sek, | ||
| - | FLOOR(avg(TIMESTAMPDIFF(SECOND,creationtime,END))/3600) AS h, | ||
| - | concat(SUBSTRING(monthname(creationtime),1,3)," ",SUBSTRING(CAST(YEAR(creationtime) AS CHAR),3,2)) as Monat | ||
| - | FROM view_instance WHERE end is not null group by Monat order by YEAR(creationtime), MONTH(creationtime) ) AS dusub | ||
| - | </code> | ||
| - | |||
| - | === Instanzen je Aktivität einer Prozessdefinition (analog zur Auswertung in den PM-Clients)=== | ||
| - | <code sql> | ||
| - | SELECT va.name AS "Aktivitäten", COUNT(va.name) AS Anzahl | ||
| - | FROM view_activity va join view_instance vi on vi.id = va.instanceId | ||
| - | WHERE va.END IS NULL | ||
| - | AND va.START IS NOT NULL | ||
| - | AND va.TYPE = "K" | ||
| - | AND vi.definitionName = "Name der Prozessdefinition" | ||
| - | and vi.archiv = false | ||
| - | GROUP BY va.name | ||
| - | </code> | ||
| - | |||
| - | === Anzahl der gestarteten und beendeten Instanzen je Monat und Definition === | ||
| - | <code sql> | ||
| - | Select Monat, Prozess, gestartete as "Anzahl gestartete Instanzen", beendete as "Anzahl beendete Instanzen" | ||
| - | from | ||
| - | (select count(distinct(inst1.id)) as gestartete, | ||
| - | concat(SUBSTRING(monthname(inst1.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst1.creationtime) AS CHAR),3,2)) as Monat, inst1.definitionname as Prozess, count(distinct(inst2.id)) as beendete, year(inst1.creationtime) as jahr, month(inst1.creationtime) as monat2 | ||
| - | from view_instance inst1 left outer join view_instance inst2 on concat(SUBSTRING(monthname(inst1.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst1.creationtime) AS CHAR),3,2))=concat(SUBSTRING(monthname(inst2.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst2.end) AS CHAR),3,2)) and inst1.definitionname=inst2.definitionname | ||
| - | group by Monat, Prozess | ||
| - | union | ||
| - | select count(distinct(inst1.id)) as gestartete, | ||
| - | concat(SUBSTRING(monthname(inst2.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst2.end) AS CHAR),3,2)) as Monat, inst2.definitionname as Prozess, count(distinct(inst2.id)) as beendete, year(inst2.end) as jahr, month(inst2.end) as monat2 | ||
| - | from view_instance inst1 right outer join view_instance inst2 on concat(SUBSTRING(monthname(inst1.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst1.creationtime) AS CHAR),3,2))=concat(SUBSTRING(monthname(inst2.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst2.end) AS CHAR),3,2)) and inst1.definitionname=inst2.definitionname | ||
| - | where inst2.end is not null group by Monat, Prozess) as spalten | ||
| - | order by jahr, monat2, Prozess | ||
| - | </code> | ||
| - | |||
| - | |||
| - | === Minimale, maximale und durchschnittliche Laufzeit (in hh:mm:ss) der Instanzen je Prozessdefinition === | ||
| - | <code sql> | ||
| - | SELECT name AS Prozess, | ||
| - | 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 Durchlaufszeit", | ||
| - | 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 Durchlaufszeit", | ||
| - | 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 Durchlaufszeit" | ||
| - | |||
| - | FROM | ||
| - | (SELECT definitionname AS name, | ||
| - | FLOOR(MIN((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))) AS minSekunden, | ||
| - | FLOOR(MIN((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))%60) AS minSek, | ||
| - | FLOOR(MIN((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime))/3600)) AS minH, | ||
| - | FLOOR(MAX((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))) AS maxSekunden, | ||
| - | FLOOR(MAX((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))%60) AS maxSek, | ||
| - | FLOOR(MAX((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime))/3600)) AS maxH, | ||
| - | FLOOR(avg((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))) AS avgSekunden, | ||
| - | FLOOR(avg((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))%60) AS avgSek, | ||
| - | FLOOR(avg((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))/3600) AS avgH, | ||
| - | (unix_timestamp(inst.END)-unix_timestamp(inst.creationtime)) AS datediff FROM view_instance inst | ||
| - | WHERE inst.END IS NOT NULL GROUP BY definitionname | ||
| - | ) AS datediffsub | ||
| - | WHERE datediff > 0 | ||
| - | GROUP BY name | ||
| - | </code> | ||
| - | |||
| - | |||
| - | === Durchschnittliche Durchlaufszeit (in hh:mm:ss) der Instanzen einer bestimmten Prozessdefinition 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 Durchlaufzeit" FROM | ||
| - | (SELECT FLOOR(avg(TIMESTAMPDIFF(SECOND,vi.creationtime,vi.END))) AS sekunden, | ||
| - | FLOOR(avg(TIMESTAMPDIFF(SECOND,vi.creationtime,vi.END))%60) AS sek, | ||
| - | FLOOR(avg(TIMESTAMPDIFF(SECOND,vi.creationtime,vi.END))/3600) AS h, | ||
| - | concat(SUBSTRING(monthname(vi.creationTime),1,3)," ",SUBSTRING(CAST(YEAR(vi.creationTime) AS CHAR),3,2)) AS Monat, | ||
| - | vi.creationtime as creationtime | ||
| - | FROM view_instance vi, view_definition vd | ||
| - | WHERE vd.id = vi.definitionId AND vi.END IS NOT NULL AND | ||
| - | vd.name = "Name der Prozessdefinition" GROUP BY YEAR(vi.creationtime), MONTH(vi.creationtime)) AS dusub | ||
| - | GROUP BY YEAR(creationtime), MONTH(creationtime) | ||
| - | </code> | ||
| - | |||
| - | |||
| - | =====Aufgabenbezogenen Auswertungen===== | ||
| - | === Alle offenen Aufgaben eines bestimmten Users === | ||
| - | <code sql> | ||
| - | select i.lastname as Nachname, i.firstname as Vorname, i.name as Username, t.name as Task, pi.name as Instanzname, pi.definitionName as Prozessname | ||
| - | from view_activity a, view_task t, view_identity i, view_instance pi | ||
| - | where a.id = t.activity and t.actor = i.id and a.end is null and t.end is null and pi.id = a.instanceId and pi.archiv = 0 and pi.end is null and i.lastname = "Barth" | ||
| - | </code> | ||
| - | |||
| - | === Anzahl an erstellten, erledigten und offenen Aufgaben je Prozessdefinition === | ||
| - | <code sql> | ||
| - | SELECT inst.definitionname AS Prozess, | ||
| - | COUNT(t1.id) AS "Anzahl erstellte Aufgaben", | ||
| - | COUNT(t2.id) AS "Anzahl erledigte Aufgaben", | ||
| - | COUNT(t3.id) AS "Anzahl offene Aufgaben" | ||
| - | FROM view_task t1 INNER JOIN view_activity act ON t1.activity=act.id | ||
| - | INNER JOIN view_instance inst ON t1.instanceid = inst.id | ||
| - | LEFT JOIN view_task t2 ON t1.id=t2.id AND (t2.end is not null OR act.END IS NOT NULL) | ||
| - | LEFT JOIN view_task t3 ON t1.id=t3.id AND t3.end is null AND inst.end is null and inst.archiv is false | ||
| - | GROUP BY inst.definitionname | ||
| - | </code> | ||
| - | === Anzahl an erstellten, erledigten und offenen Aufgaben einer bestimmten Prozessdefinition === | ||
| - | <code sql> | ||
| - | SELECT inst.definitionname AS Prozess, | ||
| - | COUNT(t1.id) AS "Anzahl erstellte Aufgaben", | ||
| - | COUNT(t2.id) AS "Anzahl erledigte Aufgaben", | ||
| - | COUNT(t3.id) AS "Anzahl offene Aufgaben" | ||
| - | FROM view_task t1 INNER JOIN view_activity act ON t1.activity=act.id INNER JOIN view_instance inst ON t1.instanceid = inst.id | ||
| - | LEFT JOIN view_task t2 ON t1.id=t2.id AND (t2.end is not null OR act.END IS NOT NULL) | ||
| - | LEFT JOIN view_task t3 ON t1.id=t3.id AND t3.end is null AND inst.end is null and inst.archiv is false | ||
| - | where inst.definitionname ="Name der Prozessdefinition" | ||
| - | </code> | ||
| - | === Durchschnittliche Zeit (in hh:mm:ss) von Erstellung bis Erledigung von allen Aufgaben (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 Aufgaben" 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_task WHERE end is not null) AS dusub | ||
| - | </code> | ||
| - | |||
| - | === Durchschnittliche Zeit (in hh:mm:ss) von Erstellung bis Erledigung von allen Aufgaben je Monat (egal von welcher Prozessdefinition 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 Aufgaben" 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_task | ||
| - | WHERE isOpen = "0" GROUP BY YEAR(START), MONTH(START)) AS dusub | ||
| - | </code> | ||
| - | |||
| - | === Durchschnittliche Zeit (in hh:mm:ss) von Erstellung bis Erledigung von allen Aufgaben je Monat ohne Ausreißer (egal von welcher Prozessdefinition 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 Aufgaben" 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_task | ||
| - | WHERE isOpen = "0" AND CAST(FLOOR((unix_timestamp(END) - unix_timestamp(START))) AS DECIMAL(10,10)) < 10000 group by YEAR(START), MONTH(START)) as dusub | ||
| - | </code> | ||
| - | |||
| - | === Alle offenen Aufgaben mit aktiver Instanz (nicht beendet und nicht archiviert) und aktiver Aktivität (nicht beendet und nicht archiviert) mit Bearbeiter === | ||
| - | <code sql> | ||
| - | select tsk.name as Aufgabe, if (concat(ident.firstname, ",",ident.lastname) is not null, | ||
| - | concat(ident.firstname, ",",ident.lastname), grouped.name) as 'Bearbeiter', | ||
| - | inst.name as 'Instanz', def.name as 'Prozess', tsk.creationTime as Start | ||
| - | from view_task tsk | ||
| - | left join view_identity ident on tsk.actor = ident.id | ||
| - | left join view_identity grouped on tsk.pooledActor = grouped.id | ||
| - | inner join view_instance inst on tsk.instanceId = inst.id | ||
| - | inner join view_definition def on inst.definitionId = def.id | ||
| - | inner join view_activity act on tsk.activity = act.id | ||
| - | where inst.archiv = 'false' and tsk.isOpen = 1 | ||
| - | </code> | ||
| - | |||
| - | |||
| - | === Durchschnittliche, minimale und maximale Durchlaufszeit aller Aufgaben einer bestimmten Prozessdefinition (in hh:mm:ss) === | ||
| - | <code sql> | ||
| - | SELECT name AS Aufgabe, | ||
| - | 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 inst.definitionname AS defName, task.name as name, | ||
| - | FLOOR(MIN((unix_timestamp(task.END) - unix_timestamp(task.START)))) AS minSekunden, | ||
| - | FLOOR(MIN((unix_timestamp(task.END) - unix_timestamp(task.START)))%60) AS minSek, | ||
| - | FLOOR(MIN((unix_timestamp(task.END) - unix_timestamp(task.START))/3600)) AS minH, | ||
| - | FLOOR(MAX((unix_timestamp(task.END) - unix_timestamp(task.START)))) AS maxSekunden, | ||
| - | FLOOR(MAX((unix_timestamp(task.END) - unix_timestamp(task.START)))%60) AS maxSek, | ||
| - | FLOOR(MAX((unix_timestamp(task.END) - unix_timestamp(task.START))/3600)) AS maxH, | ||
| - | FLOOR(avg((unix_timestamp(task.END) - unix_timestamp(task.START)))) AS avgSekunden, | ||
| - | FLOOR(avg((unix_timestamp(task.END) - unix_timestamp(task.START)))%60) AS avgSek, | ||
| - | FLOOR(avg((unix_timestamp(task.END) - unix_timestamp(task.START))/3600)) AS avgH, | ||
| - | (unix_timestamp(task.END)-unix_timestamp(task.START)) AS datediff | ||
| - | FROM view_task task INNER JOIN view_instance inst ON task.instanceId=inst.id | ||
| - | WHERE task.END IS NOT NULL AND task.START IS NOT NULL | ||
| - | GROUP BY task.name | ||
| - | ) AS datediffsub | ||
| - | WHERE datediff > 0 | ||
| - | AND defName = "Name der Prozessdefinition" | ||
| - | Group by name | ||
| - | </code> | ||
| - | |||
| - | |||
| - | |||
| - | =====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(task.id) AS "Anzahl offene Aufgaben" | ||
| - | 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_task task ON act1.id=task.activity AND task.END IS NULL AND task.START IS NOT NULL | ||
| - | LEFT JOIN view_activity act2 ON act1.id=act2.id AND act2.END 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 Aufgaben" 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), monthname(Start)) 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), monthname(START)) 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> | ||
| - | |||
| - | ======Definitionsbezogene Auswertungen====== | ||
| - | |||
| - | === Anzahl der Schleifen (>0) je Definition === | ||
| - | <code sql> | ||
| - | SELECT inst.definitionname as Prozess, SUM(act.loopcount) as Schleifendurchläufe FROM view_activity act | ||
| - | INNER JOIN view_instance inst ON inst.id=act.instanceID | ||
| - | WHERE act.loopcount IS NOT NULL | ||
| - | GROUP BY inst.definitionname | ||
| - | </code> | ||
| - | |||
| - | |||
| - | |||
| - | |||
| - | =====Variablenbezogene Auswertungen===== | ||
| - | |||
| - | === Prozessvariablen-Template === | ||
| - | <code sql> | ||
| - | INNER JOIN | ||
| - | ( | ||
| - | SELECT var1.stringvalue as `1`, var2.stringvalue as `2`, var3.stringvalue as `3`, inst.ID | ||
| - | FROM view_instance inst | ||
| - | LEFT join view_variable var1 on inst.ID = var1.instanceID and var1.name = "" | ||
| - | LEFT join view_variable var2 on inst.ID = var2.instanceID and var2.name = "" | ||
| - | LEFT join view_variable var3 on inst.ID = var3.instanceID and var3.name = "" | ||
| - | where inst.definitionname = "" AND inst.archiv = "" AND inst.end IS NOT NULL | ||
| - | ) as Variablen1 ON Variablen1.ID = Variablen2.ID | ||
| - | </code> | ||
software/dashboard/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
