Plugin installed incorrectly. Rename plugin directory 'swiftmail.backup' to 'swiftmail'.
software:dashboard:analyses:activity_analyses_oracle
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
| Nächste Überarbeitung | Vorhergehende Überarbeitung Nächste Überarbeitung Beide Seiten der Revision | ||
|
software:dashboard:analyses:activity_analyses_oracle [2015/05/20 10:24] manuel.kindler angelegt |
software:dashboard:analyses:activity_analyses_oracle [2016/04/18 11:23] oliver.schirach [Anzahl an erstellten, beendeten und offenen Aktivitäten einer bestimmten Prozessdefinition] |
||
|---|---|---|---|
| Zeile 8: | Zeile 8: | ||
| COUNT(DISTINCT(act2.id)) AS "Anzahl beendete Aktivitäten", | COUNT(DISTINCT(act2.id)) AS "Anzahl beendete Aktivitäten", | ||
| COUNT(DISTINCT(act3.id)) AS "Anzahl offene Aktivitäten" | COUNT(DISTINCT(act3.id)) AS "Anzahl offene Aktivitäten" | ||
| - | FROM view_activity act1 INNER JOIN view_instance inst ON act1.instanceid=inst.id AND act1."start" IS NOT NULL AND "type" IN ('K','S') | + | FROM view_activity act1 INNER JOIN view_instance inst ON act1.instanceid=inst.id AND act1."start" IS NOT NULL AND act1."type" IN ('K','S') |
| LEFT JOIN view_activity act2 ON act1.id=act2.id AND act2."end" IS NOT NULL | LEFT JOIN view_activity act2 ON act1.id=act2.id AND act2."end" IS NOT NULL | ||
| LEFT JOIN view_activity act3 ON act1.id=act3.id AND act3."end" IS NULL AND act3."start" IS NOT NULL | LEFT JOIN view_activity act3 ON act1.id=act3.id AND act3."end" IS NULL AND act3."start" IS NOT NULL | ||
| Zeile 20: | Zeile 20: | ||
| COUNT(DISTINCT(act2.id)) AS "Anzahl beendete Aktivitäten", | COUNT(DISTINCT(act2.id)) AS "Anzahl beendete Aktivitäten", | ||
| COUNT(DISTINCT(act3.id)) AS "Anzahl offene Aktivitäten" | COUNT(DISTINCT(act3.id)) AS "Anzahl offene Aktivitäten" | ||
| - | FROM view_activity act1 INNER JOIN view_instance inst ON act1.instanceid=inst.id AND act1."start" IS NOT NULL AND "type" IN ('K','S') AND inst.definitionName = 'PROZESSDEFINITIONSNAME' | + | FROM view_activity act1 INNER JOIN view_instance inst ON act1.instanceid=inst.id AND act1."start" IS NOT NULL AND act1."type" IN ('K','S') AND inst.definitionName = 'PROZESSDEFINITIONSNAME' |
| LEFT JOIN view_activity act2 ON act1.id=act2.id AND act2."end" IS NOT NULL | LEFT JOIN view_activity act2 ON act1.id=act2.id AND act2."end" IS NOT NULL | ||
| LEFT JOIN view_activity act3 ON act1.id=act3.id AND act3."end" IS NULL AND act3."start" IS NOT NULL | LEFT JOIN view_activity act3 ON act1.id=act3.id AND act3."end" IS NULL AND act3."start" IS NOT NULL | ||
| Zeile 61: | Zeile 61: | ||
| WHEN FLOOR(sekunden)<60 | WHEN FLOOR(sekunden)<60 | ||
| THEN | THEN | ||
| - | CONCAT(CASE WHEN FLOOR(sekunden)<10 THEN '00:00:0' ELSE '00:00:' END,FLOOR(sekunden)) | + | CONCAT(CASE WHEN FLOOR(sekunden)<10 THEN '00:00:0' ELSE '00: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)) | ||
| Zeile 75: | Zeile 75: | ||
| FLOOR(AVG((TO_NUMBER (TO_CHAR ("end", 'J')) - TO_NUMBER (TO_CHAR ("start", 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR ("end", 'SSSSS')) - TO_NUMBER (TO_CHAR ("start", 'SSSSS'))))) AS sekunden, | FLOOR(AVG((TO_NUMBER (TO_CHAR ("end", 'J')) - TO_NUMBER (TO_CHAR ("start", 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR ("end", 'SSSSS')) - TO_NUMBER (TO_CHAR ("start", 'SSSSS'))))) AS sekunden, | ||
| FLOOR(MOD(AVG((TO_NUMBER (TO_CHAR ("end", 'J')) - TO_NUMBER (TO_CHAR ("start", 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR ("end", 'SSSSS')) - TO_NUMBER (TO_CHAR ("start", 'SSSSS')))),60)) AS sek, | FLOOR(MOD(AVG((TO_NUMBER (TO_CHAR ("end", 'J')) - TO_NUMBER (TO_CHAR ("start", 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR ("end", 'SSSSS')) - TO_NUMBER (TO_CHAR ("start", 'SSSSS')))),60)) AS sek, | ||
| - | FLOOR(AVG((TO_NUMBER (TO_CHAR ("end", 'J')) - TO_NUMBER (TO_CHAR ("start", 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR ("end", 'SSSSS')) - TO_NUMBER (TO_CHAR ("start", 'SSSSS'))))/3600) AS h | + | FLOOR(AVG((TO_NUMBER (TO_CHAR ("end", 'J')) - TO_NUMBER (TO_CHAR ("start", 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR ("end", 'SSSSS')) - TO_NUMBER (TO_CHAR ("start", 'SSSSS'))))/3600) AS h, |
| + | EXTRACT(YEAR FROM to_date(SUBSTR("start",0,(INSTR("start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) as j, | ||
| + | EXTRACT(MONTH FROM to_date(SUBSTR("start",0,(INSTR("start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) as m | ||
| FROM view_activity | FROM view_activity | ||
| WHERE "end" IS NOT NULL | WHERE "end" IS NOT NULL | ||
| Zeile 82: | Zeile 84: | ||
| GROUP BY | GROUP BY | ||
| EXTRACT(YEAR FROM to_date(SUBSTR("start",0,(INSTR("start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), | EXTRACT(YEAR FROM to_date(SUBSTR("start",0,(INSTR("start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), | ||
| - | CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR("start",0,(INSTR("start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR("start",0,(INSTR("start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) | + | CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR("start",0,(INSTR("start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR("start",0,(INSTR("start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))), |
| + | EXTRACT(YEAR FROM to_date(SUBSTR("start",0,(INSTR("start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), | ||
| + | EXTRACT(MONTH FROM to_date(SUBSTR("start",0,(INSTR("start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) | ||
| ) | ) | ||
| + | ORDER BY j, m | ||
| </code> | </code> | ||
| Zeile 304: | Zeile 309: | ||
| <code sql> | <code sql> | ||
| SELECT | SELECT | ||
| + | CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS Monat, | ||
| activity."name" AS Aufgabe, | activity."name" AS Aufgabe, | ||
| CAST((avg((((to_date(SUBSTR(activity."end",0,(INSTR(activity."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) ))/3600) AS DECIMAL(10,1)) AS "Durschnittliche Durchlaufszeit", | CAST((avg((((to_date(SUBSTR(activity."end",0,(INSTR(activity."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) ))/3600) AS DECIMAL(10,1)) AS "Durschnittliche Durchlaufszeit", | ||
| Zeile 315: | Zeile 321: | ||
| AND activity."type" IN ('K','S') | AND activity."type" IN ('K','S') | ||
| AND (((to_date(SUBSTR(activity."end",0,(INSTR(activity."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) -((to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24)) > 0 | AND (((to_date(SUBSTR(activity."end",0,(INSTR(activity."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) -((to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24)) > 0 | ||
| - | AND inst.definitionname = 'PROZESSDEFINITIONSNAME' | + | AND inst.definitionname = 'NAME DER PROZESSDEFINITION' |
| - | GROUP BY activity."name" | + | GROUP BY |
| + | EXTRACT(YEAR FROM to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), | ||
| + | CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))), | ||
| + | activity."name", | ||
| + | EXTRACT(YEAR FROM to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), | ||
| + | EXTRACT(MONTH FROM to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) | ||
| + | ORDER BY | ||
| + | EXTRACT(YEAR FROM to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), | ||
| + | EXTRACT(MONTH FROM to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) | ||
| </code> | </code> | ||
software/dashboard/analyses/activity_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
