Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Nächste Überarbeitung | Vorhergehende Überarbeitung Nächste Überarbeitung Beide Seiten der Revision | ||
software:dashboard:analyses:task_analyses_oracle [2015/05/20 10:14] manuel.kindler angelegt |
software:dashboard:analyses:task_analyses_oracle [2015/06/08 10:38] manuel.kindler [Durchschnittliche, minimale und maximale Durchlaufszeit aller Aufgaben einer bestimmten Prozessdefinition (in h)] |
||
---|---|---|---|
Zeile 58: | Zeile 58: | ||
==== Durchschnittliche Zeit (in hh:mm:ss) von Erstellung bis Erledigung von allen Aufgaben je Monat (egal von welcher Prozessdefinition und auch außerhalb der Arbeitszeit) ==== | ==== Durchschnittliche Zeit (in hh:mm:ss) von Erstellung bis Erledigung von allen Aufgaben je Monat (egal von welcher Prozessdefinition und auch außerhalb der Arbeitszeit) ==== | ||
<code sql> | <code sql> | ||
- | SELECT | + | SELECT Monat, |
- | Monat, | + | |
CASE | CASE | ||
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 77: | Zeile 76: | ||
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_task | FROM view_task | ||
WHERE isOpen = '0' | WHERE isOpen = '0' | ||
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 309: | Zeile 314: | ||
<code sql> | <code sql> | ||
SELECT | SELECT | ||
+ | CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(task."start",0,(INSTR(task."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(task."start",0,(INSTR(task."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS Monat, | ||
task."name" AS Aufgabe, | task."name" AS Aufgabe, | ||
CAST((avg((((to_date(SUBSTR(task."end",0,(INSTR(task."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(task."start",0,(INSTR(task."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(task."end",0,(INSTR(task."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(task."start",0,(INSTR(task."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 319: | Zeile 325: | ||
AND task."start" IS NOT NULL | AND task."start" IS NOT NULL | ||
AND (((to_date(SUBSTR(task."end",0,(INSTR(task."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) -((to_date(SUBSTR(task."start",0,(INSTR(task."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24)) > 0 | AND (((to_date(SUBSTR(task."end",0,(INSTR(task."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - date '1970-01-01' ) * 60 * 60 * 24) -((to_date(SUBSTR(task."start",0,(INSTR(task."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 task."name" | + | GROUP BY |
+ | EXTRACT(YEAR FROM to_date(SUBSTR(task."start",0,(INSTR(task."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), | ||
+ | CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(task."start",0,(INSTR(task."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(task."start",0,(INSTR(task."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))), | ||
+ | task."name", | ||
+ | EXTRACT(YEAR FROM to_date(SUBSTR(task."start",0,(INSTR(task."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), | ||
+ | EXTRACT(MONTH FROM to_date(SUBSTR(task."start",0,(INSTR(task."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) | ||
+ | ORDER BY | ||
+ | EXTRACT(YEAR FROM to_date(SUBSTR(task."start",0,(INSTR(task."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), | ||
+ | EXTRACT(MONTH FROM to_date(SUBSTR(task."start",0,(INSTR(task."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) | ||
</code> | </code> | ||