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/09/22 14:42]
martin.oehm
@@ Zeile 1: / Zeile 1: @@
 =====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 "Average per day"
+  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 ====
 <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 30: / Zeile 46: @@
 INNER JOIN view_activity act ON act.instanceid = inst.id
 WHERE task.isopen = '1'
-AND act."end" IS NULL
+AND act.end IS NULL
 AND inst."END" IS NULL
 AND inst.archiv = 0
 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
-WHERE inst1."DEFINITIONNAME" = 'NAME DER PROZESSDEFINITION'
+WHERE inst1."DEFINITIONNAME" like 'GL-01%'
 GROUP BY inst1.definitionname
 </code>
@@ Zeile 42: / Zeile 58: @@
 <code sql>
 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
-        ELSE
+ELSE
-      END
+END) as "abgebrochen",
-    ) as "abgebrochen",
+SUM(
-    SUM(
+CASE
-      CASE
+WHEN END IS NOT NULL
-      WHEN END IS NOT NULL
+THEN
-        THEN
+ELSE
-        ELSE
+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
-        THEN
+ELSE
-        ELSE
+END) as "laufend"
+from view_instance group by definitionname
-        END
-      ) as "laufend"
-from view_instance
-group by definitionname
 </code>
@@ Zeile 87: / Zeile 99: @@
 ==== 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 119: / Zeile 139: @@
 LEFT JOIN view_identity ident
 ON task.actor = ident.id
-INNER JOIN view_identity ident2
+LEFT JOIN view_identity ident2
 ON task.pooledActor = ident2.id
 WHERE inst.archiv = 0
@@ Zeile 227: / Zeile 247: @@
 ==== 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 891: @@
 <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