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.
| Beide Seiten der vorigen Revision Vorhergehende Überarbeitung Nächste Überarbeitung | Vorhergehende Überarbeitung | ||
|
software:dashboard:analyses:activity_analyses_oracle [2015/06/08 10:49] manuel.kindler [Durchschnittliche, minimale und maximale Durchlaufszeit aller Aktivitäten einer bestimmten Prozessdefinition (in h)] |
software:dashboard:analyses:activity_analyses_oracle [2021/07/01 09:52] (aktuell) |
||
|---|---|---|---|
| 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 309: | 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", | + | CAST((MIN((((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)) "Minimale Durchlaufszeit", |
| - | CAST((MIN((((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)) "Minimale Durchlaufszeit", | + | CAST((MAX((((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 "Maximale Durchlaufszeit" |
| - | CAST((MAX((((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 "Maximale Durchlaufszeit" | + | |
| FROM view_activity activity | FROM view_activity activity | ||
| INNER JOIN | INNER JOIN | ||
| - | view_instance inst on activity.instanceId=inst.id | + | view_instance inst ON activity.instanceId=inst.id |
| - | WHERE activity."end" IS NOT NULL | + | WHERE activity.end IS NOT NULL |
| AND activity."start" IS NOT NULL | AND activity."start" IS NOT NULL | ||
| - | 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 = 'NAME DER PROZESSDEFINITION' | + | AND inst.definitionname like 'GL-01%' |
| GROUP BY | GROUP BY | ||
| - | EXTRACT(YEAR FROM to_date(SUBSTR(activity."start",0,(INSTR(activity."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), | + | activity.name |
| - | 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'))), | + | order by activity.name |
| - | 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.1433753373.txt.gz · Zuletzt geändert: 2021/07/01 10:00 (Externe Bearbeitung)
Falls nicht anders bezeichnet, ist der Inhalt dieses Wikis unter der folgenden Lizenz veröffentlicht: 2013 T!M Solutions GmbH
