Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- software:dashboard:analyses:instance_analyses_oracle [2015/06/08 10:32]
manuel.kindler [Anzahl gestarteter Instanzen je Monat für 2 Definitionen]
+++ software:dashboard:analyses:instance_analyses_oracle [2016/06/02 09:19]
martin.oehm [Anzahl an gestarteten und beendeten Instanzen je Definition]
@@ Zeile 3: / Zeile 3: @@
 <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"
+inst1.definitionname AS "Prozess", COUNT(DISTINCT(inst1.id)) AS "Anzahl gestarteter Instanzen", COUNT(DISTINCT(inst2.id)) AS "Anzahl beendeter Instanzen"
 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
+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>
@@ Zeile 87: / Zeile 79: @@
 ==== Anzahl der Schleifen (>0) je Instanz ====
 <code sql>
-SELECT inst.DEFINITIONNAME AS Prozess, inst."name" AS Instanz, (act.loopcount-1) AS Schleifendurchläufe
+SELECT
+  inst.DEFINITIONNAME AS Prozess, inst."name" AS Instanz,
+ SUM(
+  CASE
+  WHEN act.loopcount IS NOT NULL
+  THEN 1
+  ELSE 0
+  END
+  ) as Schleifendurchläufe
 FROM view_instance inst, view_activity act
 WHERE act.loopcount IS NOT NULL
 AND act.instanceID = inst.id
-GROUP BY inst.id, inst.definitionname, inst."name", act.loopcount ORDER BY inst.definitionname
+GROUP BY inst."name", inst.DEFINITIONNAME
 </code>
@@ Zeile 227: / Zeile 227: @@
 ==== Instanzen je Aktivität einer Prozessdefinition (analog zur Auswertung in den PM-Clients)===
 <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>
@@ Zeile 869: / Zeile 871: @@
 <code sql>
 SELECT
- CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS Monat,
+CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS Monat,
-  SUM(
+SUM(
-  CASE
+CASE
-  WHEN inst.definitionname ='PROZESSDEFINITIONSNAME 1'
+WHEN inst.definitionname ='PROZESSDEFINITIONSNAME 1'
-  THEN 1
+THEN 1
-  ELSE 0
+ELSE 0
-  END) AS "PROZESSDEFINITIONSNAME 1 Count",
+END) AS "PROZESSDEFINITIONSNAME 1 Count",
-  SUM(
+SUM(
-  CASE
+CASE
-  WHEN inst.definitionname ='PROZESSDEFINITIONSNAME 2'
+WHEN inst.definitionname ='PROZESSDEFINITIONSNAME 2'
-  THEN 1
+THEN 1
-  ELSE 0
+ELSE 0
-  END) AS "PROZESSDEFINITIONSNAME 2 Count"
+END) AS "PROZESSDEFINITIONSNAME 2 Count"
-  FROM view_instance inst WHERE inst.END IS NOT NULL
+FROM view_instance inst WHERE inst.END IS NOT NULL
-  AND (inst.definitionname = 'PROZESSDEFINITIONSNAME 1' OR inst.definitionname = 'PROZESSDEFINITIONSNAME 2')
+AND (inst.definitionname = 'S-AP-G' OR inst.definitionname = 'S-AP-010-010_1')
+GROUP BY
-  GROUP BY
+EXTRACT(YEAR FROM to_date(SUBSTR(END,0,(INSTR(END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')),
-  EXTRACT(YEAR FROM to_date(SUBSTR(END,0,(INSTR(END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')),
+CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))),
-  CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')))
+EXTRACT(YEAR FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')),
+EXTRACT(MONTH FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))
+ORDER BY
+EXTRACT(YEAR FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')),
+EXTRACT(MONTH FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))
 </code>

TIM Wiki / NEW TIM 6 Documentation

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Unterschiede

Seiten-Werkzeuge