Plugin installed incorrectly. Rename plugin directory 'swiftmail.backup' to 'swiftmail'.
software:dashboard:analyses:instance_analyses
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
|
software:dashboard:analyses:instance_analyses [2016/06/28 10:13] martin.oehm [Aktuelle Aktivität(en) und Bearbeiter aller offenen Instanzen] |
software:dashboard:analyses:instance_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(Cast(eff.value as Signed)) 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.DEFINITIONNAME AS Prozess, inst.name AS Instanz, SUM(IF(act.loopcount IS NOT NULL,1,0 )) AS Schleifendurchläufe | ||
| - | FROM view_instance inst, view_activity act | ||
| - | WHERE act.instanceID = inst.id | ||
| - | GROUP BY inst.id, inst.definitionname, inst.name ORDER BY inst.definitionname | ||
| - | </code> | ||
| - | ==== Aktuelle Aktivität(en) und Bearbeiter aller offenen Instanzen ==== | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | inst.name AS Prozessinstanz, | ||
| - | inst.definitionName AS Prozessdefinition, | ||
| - | group_concat(act.name SEPARATOR ', ') AS Aktivität, | ||
| - | group_concat(task.name SEPARATOR ', ') as Aufgaben, | ||
| - | group_concat(IF(ident.id IS NULL,ident2.name, concat(ident.firstname, " ",ident.lastname, " (",ident2.name,")")), '') as Username | ||
| - | 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' | ||
| - | INNER JOIN view_task task ON task.activity = act.id | ||
| - | LEFT JOIN view_identity ident ON task.actor = ident.id | ||
| - | LEFT JOIN view_identity ident2 ON task.pooledActor = ident2.id | ||
| - | WHERE inst.archiv = FALSE AND inst.END IS NULL | ||
| - | GROUP BY inst.id | ||
| - | </code> | ||
| - | |||
| - | ==== Alle offenen Instanzen mit einer bestimmten Zeichenfolge im Instanznamen ==== | ||
| - | <code sql> | ||
| - | SELECT DISTINCT vi."name" AS "Instanzname", vi."DEFINITIONNAME" AS "Prozessname" | ||
| - | FROM view_activity a, view_instance vi | ||
| - | WHERE a."end" is null | ||
| - | and a."start" IS NOT NULL | ||
| - | AND vi.id = a.instanceId | ||
| - | AND vi.archiv = 0 | ||
| - | and vi."END" is null | ||
| - | and vi."name" like '%tester%' | ||
| - | </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 Prozessdefinition 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 SUBSTRING_INDEX( va.name, '(L:', 1 ) AS "Aktivitäten", COUNT(SUBSTRING_INDEX( va.name, '(L:', 1 )) AS Anzahl | ||
| - | FROM view_activity va JOIN view_instance vi ON vi.id = va.instanceId | ||
| - | WHERE va.end IS NULL | ||
| - | AND (va.type = "K" OR va.type = "C") | ||
| - | AND va.start IS NOT NULL | ||
| - | AND vi.definitionName LIKE "PROZESSNAME" | ||
| - | AND vi.archiv = 0 | ||
| - | GROUP BY SUBSTRING_INDEX(va.name, '(L:', 1 ) | ||
| - | </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> | ||
| - | |||
| - | ==== Anzahl der gestarteten und beendeten Instanzen je Monat für eine 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 | ||
| - | where inst1.definitionname = "Name der Definition" | ||
| - | 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 and inst1.definitionname = "Name der Definition" group by Monat, Prozess) as spalten | ||
| - | order by jahr, monat2, Prozess | ||
| - | </code> | ||
| - | |||
| - | ==== Anzahl der gestarteten Instanzen je Monat (Spalten) je Jahr (Zeilen) für eine Definition ==== | ||
| - | <code sql> | ||
| - | select | ||
| - | Jahr, | ||
| - | sum(if(Monat='January',1,0)) as Januar, | ||
| - | sum(if(Monat='February',1,0)) as Februar, | ||
| - | sum(if(Monat='March',1,0)) as März, | ||
| - | sum(if(Monat='April',1,0)) as April, | ||
| - | sum(if(Monat='May',1,0)) as Mai, | ||
| - | sum(if(Monat='June',1,0)) as Juni, | ||
| - | sum(if(Monat='July',1,0)) as July, | ||
| - | sum(if(Monat='August',1,0)) as August, | ||
| - | sum(if(Monat='September',1,0)) as September, | ||
| - | sum(if(Monat='October',1,0)) as Oktober, | ||
| - | sum(if(Monat='November',1,0)) as November, | ||
| - | sum(if(Monat='December',1,0)) as Dezember | ||
| - | from | ||
| - | (select monthname(creationtime) as Monat,year(creationtime) as Jahr | ||
| - | from view_instance | ||
| - | where definitionname = 'Definitionsname' | ||
| - | ) as Base | ||
| - | group by Jahr | ||
| - | </code> | ||
| - | |||
| - | |||
| - | ====Anzahl der gestarteten Instanzen je Monat (Zeilen) je Jahr (Spalten) für eine Definition ===== | ||
| - | |||
| - | <code sql> | ||
| - | Select Monat, | ||
| - | sum(if(Jahr='2010',1,0)) as '2010', | ||
| - | sum(if(Jahr='2011',1,0)) as '2011', | ||
| - | sum(if(Jahr='2012',1,0)) as '2012', | ||
| - | sum(if(Jahr='2013',1,0)) as '2013', | ||
| - | sum(if(Jahr='2014',1,0)) as '2014' | ||
| - | from | ||
| - | (select monthname(creationtime) as Monat,year(creationtime) as Jahr, creationtime as creationtime | ||
| - | from view_instance | ||
| - | where definitionname = 'NAME DER DEFINITION' | ||
| - | ) as Base | ||
| - | group by Monat order by month(creationtime) | ||
| - | </code> | ||
| - | |||
| - | ==== Kumulierte Anzahl der gestarteten Instanzen je Monat (Spalten) je Jahr (Zeilen) für eine Definition ==== | ||
| - | <code sql> | ||
| - | select | ||
| - | Jahr, | ||
| - | sum(if(Monat='January',1,0)) as Januar, | ||
| - | sum(if(Monat='February' or Monat='January',1,0)) as Februar, | ||
| - | sum(if(Monat='March' or Monat='January' or Monat='February',1,0)) as März, | ||
| - | sum(if(Monat='April' or Monat='January' or Monat='February' or Monat='March',1,0)) as April, | ||
| - | sum(if(Monat='May' or Monat='January' or Monat='February' or Monat='March' or Monat='April',1,0)) as Mai, | ||
| - | sum(if(Monat='June' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June',1,0)) as Juni, | ||
| - | sum(if(Monat='July' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June',1,0)) as Juli, | ||
| - | sum(if(Monat='August' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June' or Monat='July',1,0)) as August, | ||
| - | sum(if(Monat='September' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June' or Monat='July' or Monat='August',1,0)) as September, | ||
| - | sum(if(Monat='October' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June' or Monat='July' or Monat='August' or Monat='September',1,0)) as Oktober, | ||
| - | sum(if(Monat='November' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June' or Monat='July' or Monat='August' or Monat='September' or Monat='October',1,0)) as November, | ||
| - | sum(if(Monat='December' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June' or Monat='July' or Monat='August' or Monat='September' or Monat='October' or Monat='November',1,0)) as Dezember | ||
| - | from | ||
| - | (select monthname(creationtime) as Monat,year(creationtime) as Jahr | ||
| - | from view_instance | ||
| - | where definitionname = 'Definitionsname' | ||
| - | ) as Base | ||
| - | group by Jahr | ||
| - | </code> | ||
| - | |||
| - | ====Kumulierte Anzahl der gestarteten Instanzen je Monat (Zeilen) je Jahr (Spalten) für eine Definition ===== | ||
| - | |||
| - | <code sql> | ||
| - | SELECT t.Monat, | ||
| - | (SELECT SUM(w.2011) | ||
| - | FROM (Select Monat,Monatszahl, | ||
| - | sum(if(Jahr='2011',1,0)) as '2011' | ||
| - | from | ||
| - | (select month(creationtime) as Monatszahl, monthname(creationtime) as Monat,year(creationtime) as Jahr | ||
| - | from view_instance | ||
| - | where definitionname = 'NAME DER DEFINITION' | ||
| - | ) as Base | ||
| - | group by Monat order by Monatszahl) as w | ||
| - | WHERE w.Monatszahl<= t.Monatszahl) AS 'Summe 2011', | ||
| - | |||
| - | |||
| - | |||
| - | (SELECT SUM(x.2012) | ||
| - | FROM (Select Monat,Monatszahl, | ||
| - | sum(if(Jahr='2012',1,0)) as '2012' | ||
| - | from | ||
| - | (select month(creationtime) as Monatszahl, monthname(creationtime) as Monat,year(creationtime) as Jahr | ||
| - | from view_instance | ||
| - | where definitionname = 'NAME DER DEFINITION' | ||
| - | ) as Base | ||
| - | group by Monat order by Monatszahl) as x | ||
| - | WHERE x.Monatszahl<= t.Monatszahl) AS 'Summe 2012',a | ||
| - | |||
| - | (SELECT SUM(y.2013) | ||
| - | FROM (Select Monat,Monatszahl, | ||
| - | sum(if(Jahr='2013',1,0)) as '2013' | ||
| - | from | ||
| - | (select month(creationtime) as Monatszahl, monthname(creationtime) as Monat,year(creationtime) as Jahr | ||
| - | from view_instance | ||
| - | where definitionname = 'NAME DER DEFINITION' | ||
| - | ) as Base | ||
| - | group by Monat order by Monatszahl) as y | ||
| - | WHERE y.Monatszahl<= t.Monatszahl) AS 'Summe 2013', | ||
| - | |||
| - | (SELECT SUM(z.2014) | ||
| - | FROM (Select Monat,Monatszahl, | ||
| - | sum(if(Jahr='2014',1,0)) as '2014' | ||
| - | from | ||
| - | (select month(creationtime) as Monatszahl, monthname(creationtime) as Monat,year(creationtime) as Jahr | ||
| - | from view_instance | ||
| - | where definitionname = 'NAME DER DEFINITION' | ||
| - | ) as Base | ||
| - | group by Monat order by Monatszahl) as z | ||
| - | WHERE z.Monatszahl<= t.Monatszahl) AS 'Summe 2014' | ||
| - | |||
| - | |||
| - | | ||
| - | | ||
| - | FROM (Select Monat,Monatszahl, | ||
| - | sum(if(Jahr='2012',1,0)) as '2012', | ||
| - | sum(if(Jahr='2013',1,0)) as '2013', | ||
| - | sum(if(Jahr='2014',1,0)) as '2014' | ||
| - | from | ||
| - | (select month(creationtime) as Monatszahl, monthname(creationtime) as Monat,year(creationtime) as Jahr | ||
| - | from view_instance | ||
| - | where definitionname = 'NAME DER DEFINITION' | ||
| - | ) as Base | ||
| - | group by Monat order by Monatszahl) as t | ||
| - | ORDER BY t.Monatszahl | ||
| - | </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> | ||
| - | |||
| - | ==== Minimale, maximale und durchschnittliche Laufzeit (in h) der Instanzen je Prozessdefinition ==== | ||
| - | <code sql> | ||
| - | SELECT definitionname AS Prozess, | ||
| - | CAST((avg((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))/3600) AS DECIMAL(10,1)) AS "Durschnittliche Durchlaufszeit", | ||
| - | CAST((MIN((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime))/3600)) AS DECIMAL(10,1)) "Minimale Durchlaufszeit", | ||
| - | CAST((MAX((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime))/3600)) AS DECIMAL(10,1)) AS "Maximale Durchlaufszeit" | ||
| - | FROM view_instance inst | ||
| - | WHERE inst.END IS NOT NULL AND (unix_timestamp(inst.END)-unix_timestamp(inst.creationtime)) > 0 GROUP BY definitionname | ||
| - | </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> | ||
| - | |||
| - | |||
| - | ==== Anzahl gestarteter Instanzen je Monat für eine Definition ==== | ||
| - | |||
| - | <code sql> | ||
| - | SELECT concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat, | ||
| - | SUM(IF(inst.definitionname ="Name der Prozessdefinition",1,0)) AS "Anzahl" | ||
| - | FROM view_instance inst WHERE inst.definitionname = "Name der Prozessdefinition" | ||
| - | GROUP BY YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC | ||
| - | </code> | ||
| - | |||
| - | |||
| - | |||
| - | ==== Anzahl gestarteter Instanzen je Monat für 2 Definitionen ==== | ||
| - | <code sql> | ||
| - | select concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat, | ||
| - | sum(IF(inst.definitionname ="Name der 1. Definition",1,0)) AS "Definition 1 Count", | ||
| - | sum(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count' | ||
| - | from view_instance inst where inst.definitionname = "Name der 1. Definition" | ||
| - | OR inst.definitionname = "Name der 2. Definition" | ||
| - | group by YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC | ||
| - | | ||
| - | </code> | ||
| - | |||
| - | ==== Anzahl gestarteter Instanzen je Monat für 3 Definitionen ==== | ||
| - | <code sql> | ||
| - | select concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat, | ||
| - | sum(IF(inst.definitionname ="Name der 1. Definition",1,0)) AS "Definition 1 Count", | ||
| - | sum(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count' , | ||
| - | sum(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count' | ||
| - | from view_instance inst where inst.definitionname = "Name der 1. Definition" | ||
| - | OR inst.definitionname = "Name der 2. Definition" | ||
| - | OR inst.definitionname = "Name der 3. Definition" | ||
| - | group by YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC | ||
| - | </code> | ||
| - | |||
| - | ==== Anzahl gestarteter Instanzen je Monat für 4 Definitionen ==== | ||
| - | <code sql> | ||
| - | select concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat, | ||
| - | sum(IF(inst.definitionname ="Name der 1. Definition",1,0)) AS 'Definition 1 Count', | ||
| - | sum(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count', | ||
| - | sum(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count', | ||
| - | sum(IF(inst.definitionname = "Name der 4. Definition",1,0)) AS 'Definition 4 Count' | ||
| - | from view_instance inst where inst.definitionname = "Name der 1. Definition" | ||
| - | OR inst.definitionname = "Name der 2. Definition" | ||
| - | OR inst.definitionname = "Name der 3. Definition" | ||
| - | OR inst.definitionname = "Name der 4. Definition" | ||
| - | group by YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC | ||
| - | </code> | ||
| - | |||
| - | ==== Anzahl gestarteter Instanzen je Monat für 5 Definitionen ==== | ||
| - | <code sql> | ||
| - | SELECT concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat, | ||
| - | SUM(IF(inst.definitionname ="Name der 1. Definition",1,0)) AS 'Definition 1 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 4. Definition",1,0)) AS 'Definition 4 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 5. Definition",1,0)) AS 'Definition 5 Count' | ||
| - | FROM view_instance inst WHERE inst.definitionname = "Name der 1. Definition" | ||
| - | OR inst.definitionname = "Name der 2. Definition" | ||
| - | OR inst.definitionname = "Name der 3. Definition" | ||
| - | OR inst.definitionname = "Name der 4. Definition" | ||
| - | OR inst.definitionname = "Name der 5. Definition" | ||
| - | GROUP BY YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC | ||
| - | |||
| - | |||
| - | </code> | ||
| - | |||
| - | |||
| - | ==== Anzahl gestarteter Instanzen je Monat für 6 Definitionen ==== | ||
| - | <code sql> | ||
| - | |||
| - | |||
| - | SELECT concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat, | ||
| - | SUM(IF(inst.definitionname ="Name der 1. Definition",1,0)) AS 'Definition 1 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 4. Definition",1,0)) AS 'Definition 4 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 5. Definition",1,0)) AS 'Definition 5 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 6. Definition",1,0)) AS 'Definition 6 Count' | ||
| - | FROM view_instance inst WHERE inst.definitionname = "Name der 1. Definition" | ||
| - | OR inst.definitionname = "Name der 2. Definition" | ||
| - | OR inst.definitionname = "Name der 3. Definition" | ||
| - | OR inst.definitionname = "Name der 4. Definition" | ||
| - | OR inst.definitionname = "Name der 5. Definition" | ||
| - | OR inst.definitionname = "Name der 6. Definition" | ||
| - | GROUP BY YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC | ||
| - | |||
| - | |||
| - | </code> | ||
| - | |||
| - | |||
| - | |||
| - | ==== Anzahl beendeter Instanzen je Monat für eine Definition ==== | ||
| - | <code sql> | ||
| - | |||
| - | SELECT concat(SUBSTRING(monthname(inst.END),1,3), " " ,SUBSTRING(CAST(YEAR(inst.END) AS CHAR),3,2)) AS Monat, | ||
| - | SUM(IF(inst.definitionname ="Name der Definition", 1,0)) AS 'Anzahl' | ||
| - | FROM view_instance inst WHERE inst.END IS NOT NULL AND | ||
| - | inst.definitionname = "Name der Definition" | ||
| - | GROUP BY YEAR(inst.END) DESC, MONTH(inst.END) DESC | ||
| - | |||
| - | </code> | ||
| - | |||
| - | |||
| - | ==== Anzahl beendeter Instanzen je Monat für 2 Definitionen ==== | ||
| - | <code sql> | ||
| - | SELECT concat(SUBSTRING(monthname(inst.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst.end) AS CHAR),3,2)) AS Monat, | ||
| - | SUM(IF(inst.definitionname ="Name der 1. Definition", 1,0)) AS 'Definition 1 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count' | ||
| - | FROM view_instance inst WHERE inst.end IS NOT NULL AND | ||
| - | (inst.definitionname = "Name der 1. Definition" | ||
| - | OR inst.definitionname = "Name der 2. Definition") | ||
| - | GROUP BY YEAR(inst.end) DESC, MONTH(inst.end) DESC | ||
| - | </code> | ||
| - | |||
| - | ==== Anzahl beendeter Instanzen je Monat für 3 Definitionen ==== | ||
| - | <code sql> | ||
| - | SELECT concat(SUBSTRING(monthname(inst.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst.end) AS CHAR),3,2)) AS Monat, | ||
| - | SUM(IF(inst.definitionname ="Name der 1. Definition",1,0)) AS 'Definition 1 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count' | ||
| - | FROM view_instance inst WHERE inst.end IS NOT NULL AND | ||
| - | (inst.definitionname = "Name der 1. Definition" | ||
| - | OR inst.definitionname = "Name der 2. Definition" | ||
| - | OR inst.definitionname = "Name der 3. Definition") | ||
| - | GROUP BY YEAR(inst.end) DESC, MONTH(inst.end) DESC | ||
| - | </code> | ||
| - | |||
| - | ==== Anzahl beendeter Instanzen je Monat für 4 Definitionen ==== | ||
| - | <code sql> | ||
| - | SELECT concat(SUBSTRING(monthname(inst.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst.end) AS CHAR),3,2)) AS Monat, | ||
| - | SUM(IF(inst.definitionname ="Name der 1. Definition", 1,0)) AS 'Definition 1 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 4. Definition",1,0)) AS 'Definition 4 Count' | ||
| - | FROM view_instance inst WHERE inst.end IS NOT NULL AND | ||
| - | (inst.definitionname = "Name der 1. Definition" | ||
| - | OR inst.definitionname = "Name der 2. Definition" | ||
| - | OR inst.definitionname = "Name der 3. Definition" | ||
| - | OR inst.definitionname = "Name der 4. Definition") | ||
| - | GROUP BY YEAR(inst.end) DESC, MONTH(inst.end) DESC | ||
| - | </code> | ||
| - | |||
| - | ==== Anzahl beendeter Instanzen je Monat für 5 Definitionen ==== | ||
| - | <code sql> | ||
| - | SELECT concat(SUBSTRING(monthname(inst.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst.end) AS CHAR),3,2)) AS Monat, | ||
| - | SUM(IF(inst.definitionname ="Name der 1. Definition", 1,0)) AS 'Definition 1 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 4. Definition",1,0)) AS 'Definition 4 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 5. Definition",1,0)) AS 'Definition 5 Count' | ||
| - | FROM view_instance inst WHERE inst.end IS NOT NULL AND | ||
| - | (inst.definitionname = "Name der 1. Definition" | ||
| - | OR inst.definitionname = "Name der 2. Definition" | ||
| - | OR inst.definitionname = "Name der 3. Definition" | ||
| - | OR inst.definitionname = "Name der 4. Definition" | ||
| - | OR inst.definitionname = "Name der 5. Definition") | ||
| - | GROUP BY YEAR(inst.end) DESC, MONTH(inst.end) DESC | ||
| - | </code> | ||
| - | |||
| - | |||
| - | ==== Anzahl beendeter Instanzen je Monat für 6 Definitionen ==== | ||
| - | <code sql> | ||
| - | |||
| - | |||
| - | SELECT concat(SUBSTRING(monthname(inst.END),1,3), " " ,SUBSTRING(CAST(YEAR(inst.END) AS CHAR),3,2)) AS Monat, | ||
| - | SUM(IF(inst.definitionname ="Name der 1. Definition", 1,0)) AS 'Definition 1 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 4. Definition",1,0)) AS 'Definition 4 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 5. Definition",1,0)) AS 'Definition 5 Count', | ||
| - | SUM(IF(inst.definitionname = "Name der 6. Definition",1,0)) AS 'Definition 6 Count' | ||
| - | FROM view_instance inst WHERE inst.END IS NOT NULL AND | ||
| - | (inst.definitionname = "Name der 1. Definition" | ||
| - | OR inst.definitionname = "Name der 2. Definition" | ||
| - | OR inst.definitionname = "Name der 3. Definition" | ||
| - | OR inst.definitionname = "Name der 4. Definition" | ||
| - | OR inst.definitionname = "Name der 5. Definition" | ||
| - | OR inst.definitionname = "Name der 6. Definition") | ||
| - | GROUP BY YEAR(inst.END) DESC, MONTH(inst.END) DESC | ||
| - | |||
| - | |||
| - | </code> | ||
software/dashboard/analyses/instance_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
