Plugin installed incorrectly. Rename plugin directory 'swiftmail.backup' to 'swiftmail'.
software:dashboard:analyses:instance_analyses_oracle
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
| Beide Seiten der vorigen Revision Vorhergehende Überarbeitung Nächste Überarbeitung | Vorhergehende Überarbeitung Nächste Überarbeitung Beide Seiten der Revision | ||
|
software:dashboard:analyses:instance_analyses_oracle [2016/03/21 09:24] manuel.kindler [Instanzen je Aktivität einer Prozessdefinition (analog zur Auswertung in den PM-Clients)] |
software:dashboard:analyses:instance_analyses_oracle [2016/09/22 14:50] martin.oehm [Durchschnittliche Anzahl an gestarteten Instanzen je Tag pro Monat (bei 22 Arbeitstagen im Monat)] |
||
|---|---|---|---|
| Zeile 1: | Zeile 1: | ||
| =====Instanzbezogene Auswertungen===== | =====Instanzbezogene Auswertungen===== | ||
| + | ==== Anzahl an gestarteten Instanzen je Tag ==== | ||
| + | <code sql> | ||
| + | SELECT | ||
| + | concat(to_char(inst.creationtime,'yy'), concat(to_char(inst.creationtime,'mm'), to_char(inst.creationtime,'dd'))) AS Tag, SUM(1) as Count | ||
| + | FROM view_instance inst | ||
| + | group by concat(to_char(inst.creationtime,'yy'), concat(to_char(inst.creationtime,'mm'), to_char(inst.creationtime,'dd'))) | ||
| + | </code> | ||
| + | |||
| + | ==== Durchschnittliche Anzahl an gestarteten Instanzen je Tag pro Monat (bei 22 Arbeitstagen im Monat) ==== | ||
| + | <code sql> | ||
| + | SELECT | ||
| + | concat(to_char(inst.creationtime,'yy'), to_char(inst.creationtime,'mm')) AS Monat, ROUND(SUM(1)/22,1) as "Durchschnitt pro Tag" | ||
| + | FROM view_instance inst | ||
| + | group by concat(to_char(inst.creationtime,'yy'), to_char(inst.creationtime,'mm')) | ||
| + | </code> | ||
| + | |||
| + | ==== Anzahl an gestarteten Instanzen je Monat ==== | ||
| + | <code sql> | ||
| + | SELECT | ||
| + | concat(to_char(inst.creationtime,'yy'), to_char(inst.creationtime,'mm')) AS Monat, SUM(1) as Anzahl | ||
| + | FROM view_instance inst | ||
| + | group by concat(to_char(inst.creationtime,'yy'), to_char(inst.creationtime,'mm')) | ||
| + | </code> | ||
| + | |||
| ==== Anzahl an gestarteten und beendeten Instanzen je Definition ==== | ==== Anzahl an gestarteten und beendeten Instanzen je Definition ==== | ||
| <code sql> | <code sql> | ||
| SELECT | 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" | + | inst1.definitionname AS "Prozess", COUNT(DISTINCT(inst1.id)) AS "Anzahl gestarteter Instanzen", COUNT(DISTINCT(inst2.id)) AS "Anzahl beendeter Instanzen" |
| FROM view_instance inst1 | FROM view_instance inst1 | ||
| LEFT JOIN view_instance inst2 | LEFT JOIN view_instance inst2 | ||
| ON inst1.id=inst2.id | ON inst1.id=inst2.id | ||
| AND inst2.END IS NOT NULL | AND inst2.END IS NOT NULL | ||
| - | INNER JOIN view_activity act | + | GROUP BY inst1.definitionname |
| - | ON act.instanceid=inst1.id | + | |
| - | LEFT JOIN view_task task | + | |
| - | ON task.instanceid=inst1.id | + | |
| - | AND task."end"= NULL | + | |
| - | AND act."start" IS NOT NULL | + | |
| - | AND act."end" = NULL | + | |
| - | AND inst1."END" = NULL AND inst1.archiv = 0 | + | |
| - | GROUP BY inst1."PROZESSDEFINITIONSNAME" | + | |
| </code> | </code> | ||
| Zeile 30: | Zeile 46: | ||
| INNER JOIN view_activity act ON act.instanceid = inst.id | INNER JOIN view_activity act ON act.instanceid = inst.id | ||
| WHERE task.isopen = '1' | WHERE task.isopen = '1' | ||
| - | AND act."end" IS NULL | + | AND act.end IS NULL |
| AND inst."END" IS NULL | AND inst."END" IS NULL | ||
| AND inst.archiv = 0 | AND inst.archiv = 0 | ||
| AND inst.definitionname = inst1."DEFINITIONNAME") AS "Anzahl offene Aufgaben" | AND inst.definitionname = inst1."DEFINITIONNAME") AS "Anzahl offene Aufgaben" | ||
| FROM view_instance inst1 LEFT JOIN view_instance inst2 ON inst1.id=inst2.id AND inst2.END IS NOT NULL | 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' | + | WHERE inst1."DEFINITIONNAME" like 'GL-01%' |
| - | GROUP BY inst1.definitionname | + | GROUP BY inst1.definitionname |
| </code> | </code> | ||
| Zeile 42: | Zeile 58: | ||
| <code sql> | <code sql> | ||
| SELECT | SELECT | ||
| - | definitionname, | + | definitionname, |
| - | SUM(1) as "alle", | + | SUM(1) as "alle", |
| - | SUM( | + | SUM( |
| - | CASE | + | CASE |
| - | WHEN archiv = 1 and END IS NULL | + | WHEN archiv = 1 and END IS NULL |
| - | THEN | + | THEN |
| - | 1 | + | 1 |
| - | ELSE | + | ELSE |
| - | 0 | + | 0 |
| - | END | + | END) as "abgebrochen", |
| - | ) as "abgebrochen", | + | SUM( |
| - | SUM( | + | CASE |
| - | CASE | + | WHEN END IS NOT NULL |
| - | WHEN END IS NOT NULL | + | THEN |
| - | THEN | + | 1 |
| - | 1 | + | ELSE |
| - | ELSE | + | 0 |
| - | 0 | + | END) as "beendet", |
| - | END | + | SUM( |
| - | ) as "beendet", | + | CASE |
| - | SUM( | + | WHEN END IS NULL AND archiv = 0 |
| - | CASE | + | THEN |
| - | WHEN END IS NULL AND archiv = 0 | + | 1 |
| - | THEN | + | ELSE |
| - | 1 | + | 0 |
| - | ELSE | + | END) as "laufend" |
| - | 0 | + | from view_instance group by definitionname |
| - | END | + | |
| - | ) as "laufend" | + | |
| - | from view_instance | + | |
| - | group by definitionname | + | |
| </code> | </code> | ||
| Zeile 127: | Zeile 139: | ||
| LEFT JOIN view_identity ident | LEFT JOIN view_identity ident | ||
| ON task.actor = ident.id | ON task.actor = ident.id | ||
| - | INNER JOIN view_identity ident2 | + | LEFT JOIN view_identity ident2 |
| ON task.pooledActor = ident2.id | ON task.pooledActor = ident2.id | ||
| WHERE inst.archiv = 0 | WHERE inst.archiv = 0 | ||
software/dashboard/analyses/instance_analyses_oracle.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
