Plugin installed incorrectly. Rename plugin directory 'swiftmail.backup' to 'swiftmail'.
software:dashboard:analyses
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
| Beide Seiten der vorigen Revision Vorhergehende Überarbeitung Nächste Überarbeitung | Vorhergehende Überarbeitung | ||
|
software:dashboard:analyses [2013/12/13 08:18] Martin.Lukas [Instanzbezogene Auswertungen] |
software:dashboard:analyses [2021/07/01 09:52] (aktuell) |
||
|---|---|---|---|
| Zeile 1: | Zeile 1: | ||
| - | =====Instanzbezogene Auswertungen===== | + | ==== MySQL ==== |
| - | === 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 === | + | [[software:dashboard:analyses:instance_analyses| Instanzbezogene Auswertungen]] |
| - | <code sql> | + | \\ [[software:dashboard:analyses:task_analyses| Aufgabenbezogenen Auswertungen]] |
| - | select inst1.definitionname as Prozess, count(inst1.id) as "Anzahl gestarteter Instanzen", count(inst2.id) as "Anzahl beendeter Instanzen", (SELECT COUNT(DISTINCT(task.id)) | + | \\ [[software:dashboard:analyses:activity_analyses| Aktivitätenbezogene Auswertungen]] |
| - | FROM view_task task INNER JOIN view_instance inst ON task.instanceid = inst.id INNER JOIN view_activity act ON act.instanceid = inst.id | + | \\ [[software:dashboard:analyses:definition_analyses| Definitionsbezogene Auswertungen]] |
| - | WHERE task.isopen IS TRUE AND act.END IS NULL AND inst.END IS NULL AND inst.archiv IS FALSE | + | \\ [[software:dashboard:analyses:variable_analyses| Variablenbezogene Auswertungen]] |
| - | 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 === | + | ==== Oracle SQL ==== |
| - | <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 === | + | [[software:dashboard:analyses:instance_analyses_oracle| Instanzbezogene Auswertungen]] |
| - | <code sql> | + | \\ [[software:dashboard:analyses:task_analyses_oracle| Aufgabenbezogenen Auswertungen]] |
| - | SELECT inst.name, act.loopcount FROM view_activity act | + | \\ [[software:dashboard:analyses:activity_analyses_oracle| Aktivitätenbezogene Auswertungen]] |
| - | INNER JOIN view_instance inst ON inst.id=act.instanceID | + | \\ [[software:dashboard:analyses:definition_analyses_oracle| Definitionsbezogene Auswertungen]] |
| - | WHERE act.loopcount IS NOT NULL group by inst.id | + | \\ [[software:dashboard:analyses:variable_analyses_oracle| Variablenbezogene Auswertungen]] |
| - | </code> | + | \\ [[software:dashboard:analyses:administrative_analyses_oracle| Administrative Auswertungen]] |
| - | + | ||
| - | === Aktuelle Aktivität(en) 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> | + | |
| - | + | ||
| - | === Durchschnittliche Durchlaufszeit (in h) von allen Instanzen aller Prozessdefinitionen je Monat === | + | |
| - | <code sql> | + | |
| - | SELECT Monat, h | + | |
| - | AS "Durchschnittliche Durchlaufzeit in Stunden" FROM | + | |
| - | (Select CAST((avg(TIMESTAMPDIFF(SECOND,creationtime,END))/3600) as DECIMAL(10,1)) 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> | + | |
| - | + | ||
| - | === Durchschnittliche Durchlaufszeit (in h) von allen Instanzen einer Prozessdefinitionen je Monat === | + | |
| - | <code sql> | + | |
| - | SELECT Monat, h | + | |
| - | AS "Durchschnittliche Durchlaufzeit in Stunden" FROM | + | |
| - | (Select CAST((avg(TIMESTAMPDIFF(SECOND,creationtime,END))/3600) as DECIMAL(10,1)) 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 and definitionname = "Name der Definition" 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 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), 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.1386919102.txt.gz · Zuletzt geändert: 2021/07/01 10:00 (Externe Bearbeitung)
Falls nicht anders bezeichnet, ist der Inhalt dieses Wikis unter der folgenden Lizenz veröffentlicht: 2013 T!M Solutions GmbH
