Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- software:dashboard:analyses:instance_analyses_oracle [2015/06/08 10:30]
manuel.kindler [Durchschnittliche Durchlaufszeit (in hh:mm:ss) der Instanzen einer bestimmten Prozessdefinition je Monat]
+++ software:dashboard:analyses:instance_analyses_oracle [2015/07/13 11:06]
manuel.kindler [Anzahl der Schleifen (>0) je Instanz]
@@ Zeile 87: / Zeile 87: @@
 ==== 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 819: / Zeile 827: @@
 <code sql>
 SELECT
- CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS Monat,
+CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS Monat,
-  SUM(
+SUM(
-  CASE
+CASE
-  WHEN inst.definitionname ='PROZESSDEFINITIONSNAME 1'
+WHEN inst.definitionname ='NAME DER PROZESSDEFINITION 1'
-  THEN 1
+THEN 1
-  ELSE 0
+ELSE 0
-  END) AS "PROZESSDEFINITIONSNAME 1 Count",
+END) AS "Count Prozessdefinition 1",
-  SUM(
+SUM(
-  CASE
+CASE
-  WHEN inst.definitionname ='PROZESSDEFINITIONSNAME 2'
+WHEN inst.definitionname ='NAME DER PROZESSDEFINITION 2'
-  THEN 1
+THEN 1
-  ELSE 0
+ELSE 0
-  END) AS "PROZESSDEFINITIONSNAME 2 Count"
+END) AS "Count Prozessdefinition 2"
-  FROM view_instance inst
+FROM view_instance inst
-  WHERE inst.definitionname = 'PROZESSDEFINITIONSNAME 1'
+WHERE inst.definitionname = 'NAME DER PROZESSDEFINITION 1'
-  OR inst.definitionname = 'PROZESSDEFINITIONSNAME 2'
+OR inst.definitionname = 'NAME DER PROZESSDEFINITION 2'
-  GROUP BY
+GROUP BY
-  EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')),
+EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')),
-  CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')))
+CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))),
+EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')),
+EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))
+ORDER BY
+EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')),
+EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))
 </code>
@@ Zeile 864: / Zeile 877: @@
 <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