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 [2015/07/13 11:06] manuel.kindler [Anzahl der Schleifen (>0) je Instanz] |
software:dashboard:analyses:instance_analyses_oracle [2016/06/28 10:12] martin.oehm [Aktuelle Aktivität(en) und Bearbeiter aller offenen Instanzen] |
||
---|---|---|---|
Zeile 3: | Zeile 3: | ||
<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 22: | ||
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 34: | ||
<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 115: | ||
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 | ||
Zeile 235: | Zeile 223: | ||
==== Instanzen je Aktivität einer Prozessdefinition (analog zur Auswertung in den PM-Clients)=== | ==== Instanzen je Aktivität einer Prozessdefinition (analog zur Auswertung in den PM-Clients)=== | ||
<code sql> | <code sql> | ||
- | SELECT SUBSTR(va."name", INSTR(va."name",'(L:')) AS "Aktivitäten", COUNT(va."name") AS Anzahl | + | SELECT |
- | FROM view_activity va JOIN view_instance vi ON vi.id = va.instanceId | + | CASE WHEN INSTR(va.name,' (L:') > 0 THEN SUBSTR(va.name, 0, INSTR(va.name,' (L:')-1) ELSE va.name END AS "Aktivitäten", |
- | WHERE va."end" is null | + | COUNT(va.name) AS Anzahl |
- | AND va."start" IS NOT NULL | + | FROM view_activity va JOIN view_instance vi ON vi.id = va.instanceId |
- | AND (va."type" = 'K' OR va."type" = 'C') | + | WHERE va.end IS NULL |
- | AND vi.definitionName = 'PROZESSDEFINITIONSNAME' | + | AND va."start" IS NOT NULL |
- | AND vi.archiv = 0 | + | AND (va.type = 'K' OR va.type = 'C') |
- | GROUP BY SUBSTR(va."name", INSTR(va."name",'(L:')) | + | AND vi.definitionName = 'PROZESSDEFINITIONSNAME' |
+ | AND vi.archiv = 0 | ||
+ | GROUP BY CASE WHEN INSTR(va.name,' (L:') > 0 THEN SUBSTR(va.name, 0, INSTR(va.name,' (L:')-1) ELSE va.name END | ||
</code> | </code> | ||