TIM Wiki / NEW TIM 6 Documentation

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Sie befinden sich hier: start » software » dashboard » analyses » instance_analyses_oracle
Zuletzt angesehen:
Plugin installed incorrectly. Rename plugin directory 'swiftmail.backup' to 'swiftmail'.
software:dashboard:analyses:instance_analyses_oracle

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung 		Vorhergehende Überarbeitung
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 [2021/07/01 09:52] (aktuell)
Zeile 1: Zeile 1:
 =====Instanzbezogene Auswertungen===== =====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 Anzahl
 +  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 "​Durchschnitt pro Tag"
 +  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 ==== ==== Anzahl an gestarteten und beendeten Instanzen je Definition ====
 <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 46:
 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 58:
 <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 87: Zeile 99:
 ==== Anzahl der Schleifen (>0) je Instanz ==== ==== Anzahl der Schleifen (>0) je Instanz ====
 <code sql> <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  FROM view_instance inst, view_activity act 
 WHERE act.loopcount IS NOT NULL  WHERE act.loopcount IS NOT NULL 
 AND act.instanceID = inst.id 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>​ </​code>​
  
Zeile 119: Zeile 139:
 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 227: Zeile 247:
 ==== 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>​
  
Zeile 819: Zeile 841:
 <code sql> <code sql>
 SELECT ​ 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>​ </​code>​
  
Zeile 864: Zeile 891:
 <code sql> <code sql>
 SELECT 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>​ </​code>​
  
software/dashboard/analyses/instance_analyses_oracle.1433752222.txt.gz · Zuletzt geändert: 2021/07/01 10:00 (Externe Bearbeitung)

Seiten-Werkzeuge

Falls nicht anders bezeichnet, ist der Inhalt dieses Wikis unter der folgenden Lizenz veröffentlicht: 2013 T!M Solutions GmbH
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki