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.
|
software:dashboard:analyses:instance_analyses_oracle [2016/09/22 14:42] martin.oehm |
software:dashboard:analyses:instance_analyses_oracle [2021/07/01 09:52] |
||
|---|---|---|---|
| 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" | ||
| - | FROM view_instance inst1 | ||
| - | LEFT JOIN view_instance inst2 | ||
| - | ON inst1.id=inst2.id | ||
| - | AND inst2.END IS NOT NULL | ||
| - | GROUP BY inst1.definitionname | ||
| - | </code> | ||
| - | |||
| - | ==== Anzahl an gestarteten und beendeten Instanzen sowie die Anzahl an offenen Aufgaben einer Prozessdefinition ==== | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | inst1."DEFINITIONNAME" AS "Prozess", | ||
| - | COUNT(inst1.id) AS "Anzahl gestarteter Instanzen", | ||
| - | COUNT(inst2.id) AS "Anzahl beendeter Instanzen", | ||
| - | (SELECT COUNT(DISTINCT(task.id)) | ||
| - | FROM view_task task | ||
| - | INNER JOIN view_instance inst ON task.instanceid = inst.id | ||
| - | INNER JOIN view_activity act ON act.instanceid = inst.id | ||
| - | WHERE task.isopen = '1' | ||
| - | 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" like 'GL-01%' | ||
| - | GROUP BY inst1.definitionname | ||
| - | </code> | ||
| - | |||
| - | ==== Anzahl an gestarteten, beendeten, abgebrochenen und laufenden Instanzen je Prozessdefinition ==== | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | definitionname, | ||
| - | SUM(1) as "alle", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN archiv = 1 and END IS NULL | ||
| - | THEN | ||
| - | 1 | ||
| - | ELSE | ||
| - | 0 | ||
| - | END) as "abgebrochen", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN END IS NOT NULL | ||
| - | THEN | ||
| - | 1 | ||
| - | ELSE | ||
| - | 0 | ||
| - | END) as "beendet", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN END IS NULL AND archiv = 0 | ||
| - | THEN | ||
| - | 1 | ||
| - | ELSE | ||
| - | 0 | ||
| - | END) as "laufend" | ||
| - | from view_instance group by definitionname | ||
| - | </code> | ||
| - | |||
| - | ==== Finanzielle Aufwände je Instanz ==== | ||
| - | <code sql> | ||
| - | SELECT inst."name" AS Instanzname, SUM(CAST(eff.VALUE AS INT)) AS Finanzaufwände | ||
| - | FROM view_effort eff | ||
| - | INNER JOIN view_instance inst | ||
| - | ON eff.instanceID=inst.id | ||
| - | WHERE eff.effortType='FINANCE' | ||
| - | GROUP BY inst.id, inst."name" | ||
| - | </code> | ||
| - | |||
| - | ==== Anzahl der Schleifen (>0) je Instanz ==== | ||
| - | <code sql> | ||
| - | 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."name", inst.DEFINITIONNAME | ||
| - | </code> | ||
| - | |||
| - | ==== Aktuelle Aktivität(en) und Bearbeiter aller offenen Instanzen ==== | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | inst."name" AS Prozessinstanz, | ||
| - | inst.definitionName AS Prozessdefinition, | ||
| - | LISTAGG(act."name", ', ') WITHIN GROUP (ORDER BY act."name") as "Aktivität", | ||
| - | LISTAGG(task."name", ', ') WITHIN GROUP (ORDER BY task."name") as "Aufgaben", | ||
| - | LISTAGG( | ||
| - | CASE | ||
| - | WHEN ident.id IS NULL | ||
| - | THEN | ||
| - | ident2."name" | ||
| - | ELSE | ||
| - | CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(ident.firstname, ' '), ident.lastname),' ('), ident2."name"),')') | ||
| - | END , | ||
| - | ', ') WITHIN GROUP (ORDER BY ident2."name") as "Username" | ||
| - | FROM view_instance inst | ||
| - | INNER JOIN view_activity act | ||
| - | ON act.instanceid = inst.id | ||
| - | AND act."start" IS NOT NULL | ||
| - | AND act."end" IS NULL AND act."type" = 'K' | ||
| - | INNER JOIN view_task task | ||
| - | ON task.activity = act.id | ||
| - | LEFT JOIN view_identity ident | ||
| - | ON task.actor = ident.id | ||
| - | LEFT JOIN view_identity ident2 | ||
| - | ON task.pooledActor = ident2.id | ||
| - | WHERE inst.archiv = 0 | ||
| - | AND inst.END IS NULL | ||
| - | GROUP BY inst.id, inst."name", inst.definitionName | ||
| - | </code> | ||
| - | |||
| - | ==== Alle offenen Instanzen mit einer bestimmten Zeichenfolge im Instanznamen ==== | ||
| - | <code sql> | ||
| - | SELECT DISTINCT vi."name" AS "Instanzname", vi."DEFINITIONNAME" AS "Prozessname" | ||
| - | FROM view_activity a, view_instance vi | ||
| - | WHERE a."end" is null | ||
| - | and a."start" IS NOT NULL | ||
| - | AND vi.id = a.instanceId | ||
| - | AND vi.archiv = 0 | ||
| - | and vi."END" is null | ||
| - | and vi."name" like '%tester%' | ||
| - | </code> | ||
| - | |||
| - | |||
| - | ==== Durchschnittliche Durchlaufszeit (in hh:mm:ss) von allen Instanzen aller Prozessdefinitionen je Monat ==== | ||
| - | <code sql> | ||
| - | SELECT Monat, | ||
| - | CASE | ||
| - | WHEN FLOOR(sekunden)<60 | ||
| - | THEN | ||
| - | CONCAT(CASE WHEN FLOOR(sekunden)<10 THEN '00:0' ELSE '00:' END,FLOOR(sekunden)) | ||
| - | WHEN FLOOR(sekunden)>3600 | ||
| - | 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) | ||
| - | 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)) | ||
| - | END as "Durchschn. DLZ" | ||
| - | FROM | ||
| - | (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(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, | ||
| - | 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 | ||
| - | 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 hh:mm:ss) der Instanzen einer bestimmten Prozessdefinition je Monat ==== | ||
| - | |||
| - | <code sql> | ||
| - | SELECT Monat, | ||
| - | CASE | ||
| - | WHEN FLOOR(sekunden)<60 | ||
| - | THEN | ||
| - | CONCAT(CASE WHEN FLOOR(sekunden)<10 THEN '00:0' ELSE '00:' END,FLOOR(sekunden)) | ||
| - | WHEN FLOOR(sekunden)>3600 | ||
| - | 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) | ||
| - | 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)) | ||
| - | END AS "Durchschn. DLZ" | ||
| - | FROM | ||
| - | (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(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, | ||
| - | 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, | ||
| - | EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) as j, | ||
| - | EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) as m | ||
| - | FROM view_instance | ||
| - | WHERE END IS NOT NULL | ||
| - | 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 Monat, h | ||
| - | AS "Durchschn. DLZ in H" FROM | ||
| - | (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, | ||
| - | 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 | ||
| - | FROM view_instance | ||
| - | WHERE END IS NOT NULL | ||
| - | GROUP BY | ||
| - | 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'))) | ||
| - | ) | ||
| - | </code> | ||
| - | |||
| - | ==== Durchschnittliche Durchlaufszeit (in h) von allen Instanzen einer Prozessdefinitionen je Monat ==== | ||
| - | <code sql> | ||
| - | SELECT Monat, h | ||
| - | AS "Durchschn. DLZ in H" FROM | ||
| - | (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, | ||
| - | 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 | ||
| - | FROM view_instance | ||
| - | WHERE END IS NOT NULL | ||
| - | AND definitionname = 'S-AP-G' | ||
| - | GROUP BY | ||
| - | 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'))) | ||
| - | ) | ||
| - | </code> | ||
| - | |||
| - | ==== Instanzen je Aktivität einer Prozessdefinition (analog zur Auswertung in den PM-Clients)=== | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | CASE WHEN INSTR(va.name,' (L:') > 0 THEN SUBSTR(va.name, 0, INSTR(va.name,' (L:')-1) ELSE va.name END AS "Aktivitäten", | ||
| - | COUNT(va.name) AS Anzahl | ||
| - | FROM view_activity va JOIN view_instance vi ON vi.id = va.instanceId | ||
| - | WHERE va.end IS NULL | ||
| - | AND va."start" IS NOT NULL | ||
| - | AND (va.type = 'K' OR va.type = 'C') | ||
| - | 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> | ||
| - | |||
| - | ==== Anzahl der gestarteten und beendeten Instanzen je Monat und Definition ==== | ||
| - | <code sql> | ||
| - | SELECT "q1Monat" as "Monat", "q1Prozess" as "Prozess", "q1Gestartete" as "Anzahl gestartete Instanzen", "q1Beendete" as "Anzahl beendete Instanzen" | ||
| - | FROM | ||
| - | ( | ||
| - | SELECT | ||
| - | COUNT(DISTINCT(inst1.id)) AS "q1Gestartete", | ||
| - | CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') ,1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS "q1Monat", | ||
| - | inst1.definitionname as "q1Prozess", | ||
| - | COUNT(DISTINCT(inst2.id)) AS "q1Beendete", | ||
| - | EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS "q1Jahr", | ||
| - | EXTRACT(MONTH FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS "q1Monat2" | ||
| - | FROM view_instance inst1 | ||
| - | LEFT OUTER JOIN view_instance inst2 | ||
| - | ON | ||
| - | to_char(to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') = to_char(to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') | ||
| - | AND | ||
| - | EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) = EXTRACT(YEAR FROM to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) | ||
| - | AND inst1.definitionname=inst2.definitionname | ||
| - | GROUP BY | ||
| - | CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') ,1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))), | ||
| - | inst1.definitionname, | ||
| - | EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), | ||
| - | EXTRACT(MONTH FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) | ||
| - | UNION | ||
| - | SELECT | ||
| - | COUNT(DISTINCT(inst1.id)) AS "q1Gestartete", | ||
| - | CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst2.END,0,(INSTR(inst2.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3), ' ') ,EXTRACT(YEAR FROM to_date(SUBSTR(inst2.END,0,(INSTR(inst2.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS "q1Monat", | ||
| - | inst2.definitionname AS "q1Prozess", | ||
| - | COUNT(DISTINCT(inst2.id)) AS "q1Beendete", | ||
| - | EXTRACT(YEAR FROM to_date(SUBSTR(inst2.creationtime,0,(INSTR(inst2.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS "q1Jahr", | ||
| - | EXTRACT(MONTH FROM to_date(SUBSTR(inst2.end,0,(INSTR(inst2.end, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS "q1Monat2" | ||
| - | FROM view_instance inst1 | ||
| - | RIGHT OUTER JOIN view_instance inst2 | ||
| - | ON | ||
| - | to_char(to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') = to_char(to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') | ||
| - | AND | ||
| - | EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) = EXTRACT(YEAR FROM to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) | ||
| - | AND inst1.definitionname=inst2.definitionname | ||
| - | WHERE inst2.END IS NOT NULL | ||
| - | GROUP BY | ||
| - | CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3), ' ') ,EXTRACT(YEAR FROM to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))), | ||
| - | inst2.definitionname, | ||
| - | EXTRACT(YEAR FROM to_date(SUBSTR(inst2.creationtime,0,(INSTR(inst2.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), | ||
| - | EXTRACT(MONTH FROM to_date(SUBSTR(inst2.end,0,(INSTR(inst2.end, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) | ||
| - | ) | ||
| - | ORDER BY "q1Jahr","q1Monat2","q1Prozess" | ||
| - | </code> | ||
| - | |||
| - | ==== Anzahl der gestarteten Instanzen je Monat (Spalten) je Jahr (Zeilen) für eine Definition ==== | ||
| - | |||
| - | <code sql> | ||
| - | SELECT | ||
| - | Jahr as "Jahr", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat LIKE '%January%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "Januar", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat LIKE '%February%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "Februar", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat LIKE '%March%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "März", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat LIKE '%April%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "April", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat LIKE '%May%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "Mai", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat LIKE '%June%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "Juni", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat LIKE '%July%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "Juli", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat LIKE '%August%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "August", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat LIKE '%September%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "September", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat LIKE '%October%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "Oktober", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat LIKE '%November%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "November", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat LIKE '%December%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "Dezember" | ||
| - | |||
| - | FROM | ||
| - | (SELECT to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY 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, | ||
| - | definitionname | ||
| - | FROM view_instance | ||
| - | WHERE definitionname = 'S-AP-G' | ||
| - | ) | ||
| - | GROUP BY Jahr | ||
| - | </code> | ||
| - | |||
| - | |||
| - | ====Anzahl der gestarteten Instanzen je Monat (Zeilen) je Jahr (Spalten) für eine Definition ===== | ||
| - | |||
| - | <code sql> | ||
| - | SELECT Monat, | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Jahr = '14' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "2014", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Jahr = '15' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "2015" | ||
| - | FROM | ||
| - | (SELECT | ||
| - | 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-YYYY HH24:MI:SS')) AS Jahr, | ||
| - | 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 = 'PROZESSDEFINITION' | ||
| - | ) | ||
| - | GROUP BY Monat,m | ||
| - | ORDER BY m | ||
| - | </code> | ||
| - | |||
| - | ==== Kumulierte Anzahl der gestarteten Instanzen je Monat (Spalten) je Jahr (Zeilen) für eine Definition ==== | ||
| - | <code sql> | ||
| - | SELECT | ||
| - | Jahr, | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat like '%January%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "Januar", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat like '%January%' OR Monat like '%February%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "Februar", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat like '%January%' OR Monat like '%February%' OR Monat like '%March%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "März", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat like '%January%' OR Monat like '%February%' OR Monat like '%March%' OR Monat like '%April%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "April", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat like '%January%' OR Monat like '%February%' OR Monat like '%March%' OR Monat like '%April%' OR Monat like '%May%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "Mai", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat like '%January%' OR Monat like '%February%' OR Monat like '%March%' OR Monat like '%April%' OR Monat like '%May%' OR Monat like '%June%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "Juni", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat like '%January%' OR Monat like '%February%' OR Monat like '%March%' OR Monat like '%April%' OR Monat like '%May%' OR Monat like '%June%' OR Monat like '%July%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "Juli", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat like '%January%' OR Monat like '%February%' OR Monat like '%March%' OR Monat like '%April%' OR Monat like '%May%' OR Monat like '%June%' OR Monat like '%July%' OR Monat like '%August%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "August", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat like '%January%' OR Monat like '%February%' OR Monat like '%March%' OR Monat like '%April%' OR Monat like '%May%' OR Monat like '%June%' OR Monat like '%July%' OR Monat like '%August%' OR MOnat like '%September%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "September", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat like '%January%' OR Monat like '%February%' OR Monat like '%March%' OR Monat like '%April%' OR Monat like '%May%' OR Monat like '%June%' OR Monat like '%July%' OR Monat like '%August%' OR MOnat like '%September%' OR Monat like '%October%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "Oktober", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat like '%January%' OR Monat like '%February%' OR Monat like '%March%' OR Monat like '%April%' OR Monat like '%May%' OR Monat like '%June%' OR Monat like '%July%' OR Monat like '%August%' OR MOnat like '%September%' OR Monat like '%October%' OR Monat like '%November%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "November", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN Monat like '%January%' OR Monat like '%February%' OR Monat like '%March%' OR Monat like '%April%' OR Monat like '%May%' OR Monat like '%June%' OR Monat like '%July%' OR Monat like '%August%' OR MOnat like '%September%' OR Monat like '%October%' OR Monat like '%November%' OR Monat like '%December%' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "Dezember" | ||
| - | FROM | ||
| - | (SELECT | ||
| - | to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY 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 | ||
| - | FROM view_instance | ||
| - | WHERE definitionname = 'PROZESSDEFINITIONSNAME' | ||
| - | ) | ||
| - | GROUP BY Jahr | ||
| - | </code> | ||
| - | |||
| - | ====Kumulierte Anzahl der gestarteten Instanzen je Monat (Zeilen) je Jahr (Spalten) für eine Definition ===== | ||
| - | |||
| - | <code sql> | ||
| - | SELECT Monat, | ||
| - | (SELECT SUM("a2015") | ||
| - | FROM | ||
| - | (SELECT aMonat, aMonatzahl, | ||
| - | SUM(CASE | ||
| - | WHEN aJahr = '15' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "a2015" | ||
| - | FROM ( | ||
| - | SELECT | ||
| - | to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') AS aMonat, | ||
| - | EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS aJahr, | ||
| - | EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) as aMonatzahl | ||
| - | FROM view_instance | ||
| - | WHERE definitionname = 'PROZESSDEFINITIONSNAME' | ||
| - | ) | ||
| - | GROUP BY aMonat, aMonatzahl | ||
| - | ORDER BY aMonatzahl | ||
| - | ) | ||
| - | WHERE aMonatzahl <= Monatzahl) as "Summe 2015", | ||
| - | | ||
| - | (SELECT SUM("b2016") | ||
| - | FROM (SELECT bMonat, bMonatzahl, | ||
| - | SUM(CASE | ||
| - | WHEN bJahr = '16' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "b2016" | ||
| - | FROM ( | ||
| - | SELECT | ||
| - | to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') AS bMonat, | ||
| - | EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS bJahr, | ||
| - | EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) as bMonatzahl | ||
| - | FROM view_instance | ||
| - | WHERE definitionname = 'PROZESSDEFINITIONSNAME' | ||
| - | ) | ||
| - | GROUP BY bMonat, bMonatzahl | ||
| - | ORDER BY bMonatzahl | ||
| - | ) | ||
| - | WHERE bMonatzahl <= Monatzahl) as "Summe 2016", | ||
| - | | ||
| - | (SELECT SUM("c2017") | ||
| - | FROM (SELECT cMonat, cMonatzahl, | ||
| - | SUM(CASE | ||
| - | WHEN cJahr = '17' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "c2017" | ||
| - | FROM ( | ||
| - | SELECT | ||
| - | to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') AS cMonat, | ||
| - | EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS cJahr, | ||
| - | EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) as cMonatzahl | ||
| - | FROM view_instance | ||
| - | WHERE definitionname = 'PROZESSDEFINITIONSNAME' | ||
| - | ) | ||
| - | GROUP BY cMonat, cMonatzahl | ||
| - | ORDER BY cMonatzahl | ||
| - | ) | ||
| - | WHERE cMonatzahl <= Monatzahl) as "Summe 2017" | ||
| - | | ||
| - | FROM | ||
| - | (Select Monat, Monatzahl, | ||
| - | SUM(CASE | ||
| - | WHEN Jahr = '15' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "2015", | ||
| - | SUM(CASE | ||
| - | WHEN Jahr = '16' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "2016", | ||
| - | SUM(CASE | ||
| - | WHEN Jahr = '17' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) as "2017" | ||
| - | From | ||
| - | (SELECT | ||
| - | to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY 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(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) as Monatzahl | ||
| - | FROM view_instance | ||
| - | WHERE definitionname = 'PROZESSDEFINITIONSNAME' | ||
| - | ) | ||
| - | GROUP BY Monat, Monatzahl | ||
| - | ORDER BY Monatzahl | ||
| - | ) | ||
| - | </code> | ||
| - | |||
| - | ==== Minimale, maximale und durchschnittliche Laufzeit (in hh:mm:ss) der Instanzen je Prozessdefinition ==== | ||
| - | <code sql> | ||
| - | 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)) | ||
| - | 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 | ||
| - | 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)) | ||
| - | 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 | ||
| - | ,'-',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 | ||
| - | 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 | ||
| - | END | ||
| - | as "Max. DLZ" | ||
| - | |||
| - | FROM | ||
| - | ( | ||
| - | |||
| - | SELECT name, minSekunden, minSek, minH, maxSekunden, maxSek, maxH, avgSekunden, avgSek, avgH | ||
| - | FROM | ||
| - | ( | ||
| - | SELECT FLOOR(MIN(( | ||
| - | ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | - | ||
| - | ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | ))) AS minSekunden, | ||
| - | definitionname as name | ||
| - | FROM view_instance inst | ||
| - | WHERE inst.END IS NOT NULL | ||
| - | group by definitionname | ||
| - | ), | ||
| - | ( | ||
| - | SELECT FLOOR(MOD(MIN(( | ||
| - | ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | - | ||
| - | ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | )),60)) AS minSek, | ||
| - | definitionname as name2 | ||
| - | FROM view_instance inst | ||
| - | WHERE inst.END IS NOT NULL | ||
| - | group by definitionname | ||
| - | ), | ||
| - | ( | ||
| - | SELECT FLOOR(MIN(( | ||
| - | ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | - | ||
| - | ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | )/3600)) AS minH, | ||
| - | definitionname as name3 | ||
| - | FROM view_instance inst | ||
| - | WHERE inst.END IS NOT NULL | ||
| - | group by definitionname | ||
| - | ), | ||
| - | (SELECT FLOOR(MAX(( | ||
| - | ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | - | ||
| - | ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | ))) AS maxSekunden, | ||
| - | definitionname as name4 | ||
| - | FROM view_instance inst | ||
| - | WHERE inst.END IS NOT NULL | ||
| - | group by definitionname | ||
| - | ), | ||
| - | ( | ||
| - | SELECT FLOOR(MOD(MAX(( | ||
| - | ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | - | ||
| - | ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | )),60)) AS maxSek, | ||
| - | definitionname as name5 | ||
| - | FROM view_instance inst | ||
| - | WHERE inst.END IS NOT NULL | ||
| - | group by definitionname | ||
| - | ), | ||
| - | ( | ||
| - | SELECT FLOOR(MAX(( | ||
| - | ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | - | ||
| - | ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | )/3600)) AS maxH, | ||
| - | definitionname as name6 | ||
| - | FROM view_instance inst | ||
| - | WHERE inst.END IS NOT NULL | ||
| - | group by definitionname | ||
| - | ), | ||
| - | ( | ||
| - | SELECT FLOOR(avg(( | ||
| - | ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | - | ||
| - | ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | ))) AS avgSekunden, | ||
| - | definitionname as name7 | ||
| - | FROM view_instance inst | ||
| - | WHERE inst.END IS NOT NULL | ||
| - | group by definitionname | ||
| - | ), | ||
| - | ( | ||
| - | SELECT FLOOR(MOD(avg(( | ||
| - | ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | - | ||
| - | ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | )),60)) AS avgSek, | ||
| - | definitionname as name8 | ||
| - | FROM view_instance inst | ||
| - | WHERE inst.END IS NOT NULL | ||
| - | group by definitionname | ||
| - | ), | ||
| - | ( | ||
| - | SELECT FLOOR(avg(( | ||
| - | ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | - | ||
| - | ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) | ||
| - | ))/3600) AS avgH, | ||
| - | definitionname as name9 | ||
| - | FROM view_instance inst | ||
| - | WHERE inst.END IS NOT NULL | ||
| - | group by definitionname | ||
| - | ) | ||
| - | where name = name2 | ||
| - | and name = name3 | ||
| - | and name = name4 | ||
| - | and name = name5 | ||
| - | and name = name6 | ||
| - | and name = name7 | ||
| - | and name = name8 | ||
| - | and name = name9 | ||
| - | ) | ||
| - | </code> | ||
| - | |||
| - | ==== Minimale, maximale und durchschnittliche Laufzeit (in h) der Instanzen je Prozessdefinition ==== | ||
| - | <code sql> | ||
| - | SELECT definitionname AS Prozess, | ||
| - | CAST((avg((((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) ))/3600) AS DECIMAL(10,1)) AS "Durschnittliche Durchlaufszeit", | ||
| - | CAST((MIN((((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24))/3600)) AS DECIMAL(10,1)) "Minimale Durchlaufszeit", | ||
| - | CAST((MAX((((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24))/3600)) AS DECIMAL(10,1)) AS "Maximale Durchlaufszeit" | ||
| - | FROM view_instance inst | ||
| - | WHERE inst.END IS NOT NULL AND (((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) -((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24)) > 0 GROUP BY definitionname | ||
| - | </code> | ||
| - | |||
| - | |||
| - | ==== Anzahl gestarteter Instanzen je Monat für eine Definition ==== | ||
| - | |||
| - | <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, | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN inst.definitionname ='PROZESSDEFINITIONSNAME' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "Anzahl" | ||
| - | FROM view_instance inst WHERE inst.definitionname = 'PROZESSDEFINITIONSNAME' | ||
| - | GROUP BY | ||
| - | 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'))) | ||
| - | </code> | ||
| - | |||
| - | |||
| - | |||
| - | ==== Anzahl gestarteter Instanzen je Monat für 2 Definitionen ==== | ||
| - | <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, | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN inst.definitionname ='NAME DER PROZESSDEFINITION 1' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "Count Prozessdefinition 1", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN inst.definitionname ='NAME DER PROZESSDEFINITION 2' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "Count Prozessdefinition 2" | ||
| - | FROM view_instance inst | ||
| - | WHERE inst.definitionname = 'NAME DER PROZESSDEFINITION 1' | ||
| - | OR inst.definitionname = 'NAME DER PROZESSDEFINITION 2' | ||
| - | GROUP BY | ||
| - | 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'))), | ||
| - | 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> | ||
| - | |||
| - | |||
| - | ==== Anzahl beendeter Instanzen je Monat für eine Definition ==== | ||
| - | <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, | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN inst.definitionname ='PROZESSDEFINITIONSNAME' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "PROZESSDEFINITIONSNAME Count" | ||
| - | FROM view_instance inst WHERE inst.END IS NOT NULL AND | ||
| - | inst.definitionname = 'PROZESSDEFINITIONSNAME' | ||
| - | GROUP BY | ||
| - | 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'))) | ||
| - | |||
| - | </code> | ||
| - | |||
| - | |||
| - | ==== Anzahl beendeter Instanzen je Monat für 2 Definitionen ==== | ||
| - | <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, | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN inst.definitionname ='PROZESSDEFINITIONSNAME 1' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "PROZESSDEFINITIONSNAME 1 Count", | ||
| - | SUM( | ||
| - | CASE | ||
| - | WHEN inst.definitionname ='PROZESSDEFINITIONSNAME 2' | ||
| - | THEN 1 | ||
| - | ELSE 0 | ||
| - | END) AS "PROZESSDEFINITIONSNAME 2 Count" | ||
| - | FROM view_instance inst WHERE inst.END IS NOT NULL | ||
| - | AND (inst.definitionname = 'S-AP-G' OR inst.definitionname = 'S-AP-010-010_1') | ||
| - | GROUP BY | ||
| - | 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'))), | ||
| - | 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> | ||
software/dashboard/analyses/instance_analyses_oracle.txt · Zuletzt geändert: 2021/07/01 09:52 (Externe Bearbeitung)
Falls nicht anders bezeichnet, ist der Inhalt dieses Wikis unter der folgenden Lizenz veröffentlicht: 2013 T!M Solutions GmbH
