Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- software:dashboard:analyses:instance_analyses_oracle [2015/05/27 09:44]
manuel.kindler
+++ software:dashboard:analyses:instance_analyses_oracle [2021/07/01 09:52] (aktuell)
@@ 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 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 ====
 <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 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
+GROUP BY inst."name", inst.DEFINITIONNAME
-ORDER BY inst.definitionname
 </code>
@@ Zeile 120: / 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 173: / Zeile 192: @@
 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>
 ==== Durchschnittliche Durchlaufszeit (in h) von allen Instanzen aller Prozessdefinitionen je Monat ====
 <code sql>
-SELECT
+SELECT Monat, h
-Monat,
+AS "Durchschn. DLZ in H" FROM
-h AS "Durchschnittliche Durchlaufzeit in Stunden"
+(SELECT CAST((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 DECIMAL(10,1)) AS h,
-FROM
+CONCAT(CONCAT( 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'))) AS Monat
-  (SELECT
+FROM view_instance
-  CAST((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 DECIMAL(10,1)) AS h,
+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'))) AS Monat
+GROUP BY
-  FROM view_instance
+CONCAT(CONCAT( 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')))
-  WHERE END IS NOT NULL
+)
-  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')))
- )
 </code>
 ==== Durchschnittliche Durchlaufszeit (in h) von allen Instanzen einer Prozessdefinitionen je Monat ====
 <code sql>
-SELECT
+SELECT Monat, h
-Monat,
+AS "Durchschn. DLZ in H" FROM
-h AS "Durchschn. DLZ in H"
+(SELECT CAST((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 DECIMAL(10,1)) AS h,
-FROM
+CONCAT(CONCAT( 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'))) as Monat
-  (SELECT
+FROM view_instance
-  CAST((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 DECIMAL(10,1)) AS h,
+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'))) AS Monat
+AND definitionname = 'S-AP-G'
-  FROM view_instance
+GROUP BY
-  WHERE END IS NOT NULL
+CONCAT(CONCAT( 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')))
-  AND definitionname = 'PROZESSDEFINITIONSNAME'
+)
-  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')))
- )
 </code>
 ==== 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 292: / Zeile 312: @@
 <code sql>
 SELECT
-  Jahr,
+  Jahr as "Jahr",
   SUM(
     CASE
-    WHEN Monat like '%January%'
+    WHEN Monat LIKE '%January%'
     THEN 1
     ELSE 0
-    END) as "Januar",
+    END) AS "Januar",
    SUM(
     CASE
-    WHEN Monat like '%February%'
+    WHEN Monat LIKE '%February%'
     THEN 1
     ELSE 0
-    END) as Februar,
+    END) AS "Februar",
   SUM(
     CASE
-    WHEN Monat like '%March%'
+    WHEN Monat LIKE '%March%'
     THEN 1
     ELSE 0
-    END) as "März",
+    END) AS "März",
   SUM(
     CASE
-    WHEN Monat like '%April%'
+    WHEN Monat LIKE '%April%'
     THEN 1
     ELSE 0
-    END) as "April",
+    END) AS "April",
    SUM(
     CASE
-    WHEN Monat like '%May%'
+    WHEN Monat LIKE '%May%'
     THEN 1
     ELSE 0
-    END) as "Mai",
+    END) AS "Mai",
   SUM(
     CASE
-    WHEN Monat like '%June%'
+    WHEN Monat LIKE '%June%'
     THEN 1
     ELSE 0
-    END) as "Juni",
+    END) AS "Juni",
   SUM(
     CASE
-    WHEN Monat like '%July%'
+    WHEN Monat LIKE '%July%'
     THEN 1
     ELSE 0
-    END) as "Juli",
+    END) AS "Juli",
    SUM(
     CASE
-    WHEN Monat like '%August%'
+    WHEN Monat LIKE '%August%'
     THEN 1
     ELSE 0
-    END) as "August",
+    END) AS "August",
   SUM(
     CASE
-    WHEN Monat like '%September%'
+    WHEN Monat LIKE '%September%'
     THEN 1
     ELSE 0
-    END) as "September",
+    END) AS "September",
   SUM(
     CASE
-    WHEN Monat like '%October%'
+    WHEN Monat LIKE '%October%'
     THEN 1
     ELSE 0
-    END) as "Oktober",
+    END) AS "Oktober",
    SUM(
     CASE
-    WHEN Monat like '%November%'
+    WHEN Monat LIKE '%November%'
     THEN 1
     ELSE 0
-    END) as "November",
+    END) AS "November",
    SUM(
     CASE
-    WHEN Monat like '%December%'
+    WHEN Monat LIKE '%December%'
     THEN 1
     ELSE 0
-    END) as "Dezember"
+    END) AS "Dezember"
 FROM
@@ Zeile 371: / Zeile 391: @@
     definitionname
     FROM view_instance
-    WHERE definitionname = 'PROZESSDEFINITIONNAME'
+    WHERE definitionname = 'S-AP-G'
   )
-Group by Jahr
+GROUP BY Jahr
 </code>
@@ Zeile 381: / Zeile 401: @@
 <code sql>
 SELECT Monat,
-  SUM(
+SUM(
-    CASE
+CASE
-    WHEN Jahr = '14'
+WHEN Jahr = '14'
-    THEN 1
+THEN 1
-    ELSE 0
+ELSE 0
-    END) as "2014",
+END) as "2014",
-  SUM(
+SUM(
-    CASE
+CASE
-    WHEN Jahr = '15'
+WHEN Jahr = '15'
-    THEN 1
+THEN 1
-    ELSE 0
+ELSE 0
-    END) as "2015"
+END) as "2015"
-  FROM
+FROM
 (SELECT
- to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') AS Monat,
+to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'), 'Month') AS Monat,
- EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS Jahr,
+EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')) AS Jahr,
- definitionname
+EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) as m,
+definitionname
 FROM view_instance vi
-WHERE definitionname = 'PROZESSDEFINITIONSNAME'
+WHERE definitionname = 'PROZESSDEFINITION'
 )
+GROUP BY Monat,m
+ORDER BY m
 </code>
@@ Zeile 588: / Zeile 611: @@
 SELECT
 name as "Prozess",
+CASE WHEN
+INSTR(CASE
+WHEN FLOOR(avgSekunden)<60
+THEN
+CONCAT(CASE WHEN FLOOR(avgSekunden)<10 THEN '00:00:0' ELSE '00:00:' END,FLOOR(avgSekunden))
+WHEN FLOOR(avgSekunden)>3600
+THEN
+CONCAT(CONCAT(CONCAT(CONCAT(avgH, CASE WHEN FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60)<10 THEN ':0' ELSE ':' END), FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60)), CASE WHEN avgSek<10 THEN ':0' ELSE ':' END), avgSek)
+ELSE
+CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(avgSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(avgSekunden/60)), CASE WHEN FLOOR(avgSek)<10 THEN ':0' ELSE ':' END), FLOOR(avgSek))
+END,'-',1,1)
+>0
+THEN '00:00:00'
+ELSE
 CASE
 WHEN FLOOR(avgSekunden)<60
 THEN
-  CONCAT(CASE WHEN FLOOR(avgSekunden)<10 THEN '00:00:0' ELSE '00:00:' END,FLOOR(avgSekunden))
+CONCAT(CASE WHEN FLOOR(avgSekunden)<10 THEN '00:00:0' ELSE '00:00:' END,FLOOR(avgSekunden))
 WHEN FLOOR(avgSekunden)>3600
 THEN
-  CONCAT(CONCAT(CONCAT(CONCAT(avgH, CASE WHEN FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60)<10 THEN ':0' ELSE ':' END), FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60)), CASE WHEN avgSek<10 THEN ':0' ELSE ':' END), avgSek)
+CONCAT(CONCAT(CONCAT(CONCAT(avgH, CASE WHEN FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60)<10 THEN ':0' ELSE ':' END), FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60)), CASE WHEN avgSek<10 THEN ':0' ELSE ':' END), avgSek)
 ELSE
-  CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(avgSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(avgSekunden/60)), CASE WHEN FLOOR(avgSek)<10 THEN ':0' ELSE ':' END), FLOOR(avgSek))
+CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(avgSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(avgSekunden/60)), CASE WHEN FLOOR(avgSek)<10 THEN ':0' ELSE ':' END), FLOOR(avgSek))
 END
-AS "Durschn. DLZ",
+END AS "Durschn. DLZ",
+CASE WHEN
+INSTR(
 CASE
 WHEN FLOOR(minSekunden)<60
 THEN
-  CONCAT(CASE WHEN FLOOR(minSekunden)<10 THEN '00:00:0' ELSE '00:00:' END, FLOOR(minSekunden))
+CONCAT(CASE WHEN FLOOR(minSekunden)<10 THEN '00:00:0' ELSE '00:00:' END, FLOOR(minSekunden))
 WHEN FLOOR(minSekunden)>3600
 THEN
-  CONCAT(CONCAT(CONCAT(CONCAT(minH,CASE WHEN FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60)<10 THEN ':0' ELSE ':' END),FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60)),CASE WHEN minSek<10 THEN ':0' ELSE ':' END) ,minSek)
+CONCAT(CONCAT(CONCAT(CONCAT(minH,CASE WHEN FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60)<10 THEN ':0' ELSE ':' END),FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60)),CASE WHEN minSek<10 THEN ':0' ELSE ':' END) ,minSek)
+ELSE
+CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(minSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(minSekunden/60)),CASE WHEN FLOOR(minSek)<10 THEN ':0' ELSE ':' END),FLOOR(minSek))
+END
+,'-',1,1)
+>0
+THEN '00:00:00'
+ELSE
+CASE
+WHEN FLOOR(minSekunden)<60
+THEN
+CONCAT(CASE WHEN FLOOR(minSekunden)<10 THEN '00:00:0' ELSE '00:00:' END, FLOOR(minSekunden))
+WHEN FLOOR(minSekunden)>3600
+THEN
+CONCAT(CONCAT(CONCAT(CONCAT(minH,CASE WHEN FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60)<10 THEN ':0' ELSE ':' END),FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60)),CASE WHEN minSek<10 THEN ':0' ELSE ':' END) ,minSek)
+ELSE
+CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(minSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(minSekunden/60)),CASE WHEN FLOOR(minSek)<10 THEN ':0' ELSE ':' END),FLOOR(minSek))
+END
+END
+ as "Min. DLZ",
+CASE WHEN
+INSTR(
+CASE
+WHEN FLOOR(maxSekunden)<60
+THEN
+CONCAT(CASE WHEN FLOOR(maxSekunden)<10 THEN '00:00:0' ELSE '00:00:' END, FLOOR(maxSekunden))
+WHEN FLOOR(maxSekunden)>3600
+THEN
+CONCAT(CONCAT(CONCAT(CONCAT(maxH,CASE WHEN FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60)<10 THEN ':0' ELSE ':' END),FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60)),CASE WHEN maxSek<10 THEN ':0' ELSE ':' END) ,maxSek)
+ELSE
+CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(maxSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(maxSekunden/60)),CASE WHEN FLOOR(maxSek)<10 THEN ':0' ELSE ':' END),FLOOR(maxSek))
+END,'-',1,1)
+>0
+THEN '00:00:00'
 ELSE
-  CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(minSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(minSekunden/60)),CASE WHEN FLOOR(minSek)<10 THEN ':0' ELSE ':' END),FLOOR(minSek))
-END as "Min. DLZ",
 CASE
 WHEN FLOOR(maxSekunden)<60
 THEN
-  CONCAT(CASE WHEN FLOOR(maxSekunden)<10 THEN '00:00:0' ELSE '00:00:' END, FLOOR(maxSekunden))
+CONCAT(CASE WHEN FLOOR(maxSekunden)<10 THEN '00:00:0' ELSE '00:00:' END, FLOOR(maxSekunden))
 WHEN FLOOR(maxSekunden)>3600
 THEN
-  CONCAT(CONCAT(CONCAT(CONCAT(maxH,CASE WHEN FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60)<10 THEN ':0' ELSE ':' END),FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60)),CASE WHEN maxSek<10 THEN ':0' ELSE ':' END) ,maxSek)
+CONCAT(CONCAT(CONCAT(CONCAT(maxH,CASE WHEN FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60)<10 THEN ':0' ELSE ':' END),FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60)),CASE WHEN maxSek<10 THEN ':0' ELSE ':' END) ,maxSek)
 ELSE
-  CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(maxSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(maxSekunden/60)),CASE WHEN FLOOR(maxSek)<10 THEN ':0' ELSE ':' END),FLOOR(maxSek))
+CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(maxSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(maxSekunden/60)),CASE WHEN FLOOR(maxSek)<10 THEN ':0' ELSE ':' END),FLOOR(maxSek))
-END as "Max. DLZ"
+END
+END
+as "Max. DLZ"
 FROM
@@ Zeile 725: / Zeile 797: @@
 group by definitionname
 )
 where name = name2
 and name = name3
 and name = name4
@@ Zeile 733: / Zeile 805: @@
 and name = name8
 and name = name9
 )
 </code>
@@ Zeile 769: / Zeile 841: @@
 <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 814: / 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