Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- software:dashboard:analyses:instance_analyses_oracle [2015/06/03 15:24]
manuel.kindler [Anzahl der gestarteten Instanzen je Monat (Zeilen) je Jahr (Spalten) für eine Definition]
+++ 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 172: / Zeile 172: @@
 WHEN FLOOR(sekunden)<60
 THEN
-  CONCAT(CASE WHEN FLOOR(sekunden)<10 THEN '00:0' ELSE '00:' END,FLOOR(sekunden))
+CONCAT(CASE WHEN FLOOR(sekunden)<10 THEN '00:0' ELSE '00:' END,FLOOR(sekunden))
-  WHEN FLOOR(sekunden)>3600
+WHEN FLOOR(sekunden)>3600
-  THEN
+THEN
-    CONCAT(CONCAT(CONCAT(CONCAT(h,CASE WHEN FLOOR (FLOOR(sekunden)- h*3600-sek)/60<10 THEN ':0' ELSE ':' END),FLOOR(FLOOR(sekunden)- h*3600-sek)/60),CASE WHEN sek<10 THEN ':0' ELSE ':' END),sek)
+CONCAT(CONCAT(CONCAT(CONCAT(h,CASE WHEN FLOOR (FLOOR(sekunden)- h*3600-sek)/60<10 THEN ':0' ELSE ':' END),FLOOR(FLOOR(sekunden)- h*3600-sek)/60),CASE WHEN sek<10 THEN ':0' ELSE ':' END),sek)
-  ELSE
+ELSE
-    CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(sekunden/60)<10 THEN '00:0' ELSE '00:' END ,FLOOR(sekunden/60)), CASE WHEN FLOOR(sek)<10 THEN ':0' ELSE ':' END),FLOOR(sek))
+CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(sekunden/60)<10 THEN '00:0' ELSE '00:' END ,FLOOR(sekunden/60)), CASE WHEN FLOOR(sek)<10 THEN ':0' ELSE ':' END),FLOOR(sek))
-  END AS "Durchschn. DLZ"
+END AS "Durchschn. DLZ"
-  FROM
+FROM
-  (SELECT
+(SELECT
-    FLOOR(AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS'))))) AS sekunden,
+FLOOR(AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS'))))) AS sekunden,
-    FLOOR(MOD(AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS')))),60)) AS sek,
+FLOOR(MOD(AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS')))),60)) AS sek,
-    FLOOR(AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS'))))/3600) AS h,
+FLOOR(AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS'))))/3600) AS h,
-    CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS Monat
+CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS Monat,
-    FROM view_instance
+EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) as j,
-    WHERE END IS NOT NULL
+EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) as m
-    AND definitionName = 'NAME DER PROZESSDEFINITION'
+FROM view_instance
-    GROUP BY
+WHERE END IS NOT NULL
-    CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')))
+AND definitionName = 'NAME DER PROZESSDEFINITION'
-  )
+GROUP BY
+CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),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')),
+EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))
+)
+ORDER BY j, m
   </code>
@@ Zeile 222: / 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 814: / Zeile 821: @@
 <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 859: / 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