TIM Wiki / NEW TIM 6 Documentation

User Tools

Site Tools

You are here: start » en » software » dashboard » analyses » task_analyses_oracle
Trace: • task_analyses_oracle
Plugin installed incorrectly. Rename plugin directory 'swiftmail.backup' to 'swiftmail'.
This translation is older than the original page and might be outdated. See what has changed.
en:software:dashboard:analyses:task_analyses_oracle

Table of Contents

FIXME This page is not fully translated, yet. Please help completing the translation.
(remove this paragraph once the translation is finished)

All open tasks for a specified user

SELECT i.lastname AS Nachname, i.firstname AS Vorname, i."name" AS Username, t."name" AS Task, vi."name" AS "Instance name", vi.definitionName AS Prozessname 
FROM view_activity a, view_task t, view_identity i, view_instance vi
WHERE a.id = t.activity AND t.actor = i.id AND a."end" IS NULL AND t."end" IS NULL AND vi.id = a.instanceId AND vi.archiv = 0 AND vi.END IS NULL AND i.lastname = 'NACHNAME'​

Number of created, completed, and open tasks for each process definition

SELECT inst.definitionname AS Prozess,
COUNT(t1.id) AS "Number of created tasks",
COUNT(t2.id) AS "Number of completed tasks",
COUNT(t3.id) AS "Number of open tasks"
FROM view_task t1 INNER JOIN view_activity act ON t1.activity=act.id
INNER JOIN view_instance inst ON t1.instanceid = inst.id
LEFT JOIN view_task t2 ON t1.id=t2.id AND (t2."end" IS NOT NULL OR act."end" IS NOT NULL)
LEFT JOIN view_task t3 ON t1.id=t3.id AND t3."end" IS NULL AND inst.END IS NULL AND inst.archiv = 0
GROUP BY inst.definitionname​

Number of created, completed, and open tasks for a specified process definition

SELECT inst.definitionname AS Prozess,
COUNT(t1.id) AS "Number of created tasks",
COUNT(t2.id) AS "Number of completed tasks",
COUNT(t3.id) AS "Number of open tasks"
FROM view_task t1 INNER JOIN view_activity act ON t1.activity=act.id INNER JOIN view_instance inst ON t1.instanceid = inst.id
LEFT JOIN view_task t2 ON t1.id=t2.id AND (t2."end" IS NOT NULL OR act."end" IS NOT NULL)
LEFT JOIN view_task t3 ON t1.id=t3.id AND t3."end" IS NULL AND inst.END IS NULL AND inst.archiv = 0
WHERE inst.definitionname ='PROZESSDEFINITIONSNAME'
  GROUP BY inst.definitionname​

Average time (in hh:mm:ss) from creation to completion of all tasks (regardless of from which process definition they come and including time outside of working hours)

SELECT
CASE
WHEN FLOOR(sekunden)<60
THEN
  CONCAT(CASE WHEN FLOOR(sekunden)<10 THEN  '00:00:0' ELSE '00: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 "Avg. LT of Tasks"
FROM (
SELECT
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(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
FROM view_task WHERE "end" IS NOT NULL
) ​

Average time (in hh:mm:ss) from creation to completion of all tasks per month (regardless of from which process definition they come and including time outside of working hours)

SELECT Monat,
CASE
WHEN FLOOR(sekunden)<60
THEN
CONCAT(CASE WHEN FLOOR(sekunden)<10 THEN  '00:00:0' ELSE '00: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 "Avg. LT of Tasks"
FROM
(
SELECT 
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'))) AS Monat,
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(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 
WHERE isOpen = '0' 
GROUP BY 
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

Average time (in hh:mm:ss) from creation to completion of all tasks per month, excluding outliers (regardless of from which process definition they come and including time outside of working hours)

SELECT
Monat,
CASE
WHEN FLOOR(sekunden)<60
THEN
  CONCAT(CASE WHEN FLOOR(sekunden)<10 THEN  '00:00:0' ELSE '00: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 "Avg. LT of Tasks"
FROM
(
SELECT 
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'))) AS Monat,
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(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
FROM view_task
WHERE isOpen = '0' 
AND 
(((to_date(SUBSTR("end",0,(INSTR("end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24) -((to_date(SUBSTR("start",0,(INSTR("start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24)) < 10000
GROUP BY 
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')))
)

All open tasks with an active instance (not finished and not archived) and active activity (not finished and not archived) with user

SELECT tsk."name" AS Aufgabe,
inst."name" AS "Instanz",
CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(ident.firstname, ' '), ident.lastname),' ('), grouped."name"),')') AS "PersResp",
def."name" AS "Prozess", 
tsk.creationTime AS "CreationTime"
FROM view_task tsk
LEFT JOIN view_identity ident ON tsk.actor = ident.id
LEFT JOIN view_identity grouped ON tsk.pooledActor = grouped.id
INNER JOIN view_instance inst ON tsk.instanceId = inst.id
INNER JOIN view_definition def ON inst.definitionId = def.id
INNER JOIN view_activity act ON tsk.activity = act.id
WHERE inst.archiv = '0' AND tsk.isOpen = '1'​

Average, minimum and maximum lead time for all tasks of a specified process definition (in hh:mm:ss)

SELECT 
name1 AS "Task",
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 
AS "Avg. LT",
 
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 AS "Min. LT",
 
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 AS "Max. LT"
 
FROM
(
 
SELECT name1, minSekunden, minSek, minH, maxSekunden, maxSek, maxH, avgSekunden, avgSek, avgH
FROM
(
SELECT FLOOR(MIN((
((to_date(SUBSTR(ta."end",0,(INSTR(ta."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24) 
- 
((to_date(SUBSTR(ta."start",0,(INSTR(ta."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24)
))) AS minSekunden,
ta."name" AS name1
FROM view_task ta, view_instance vi
WHERE vi.definitionName = 'PROZESSDEFINITIONSNAME'
AND vi.id = ta.instanceId
AND ta."end" IS NOT NULL 
GROUP BY ta."name"​
),
(
SELECT FLOOR(MOD(MIN((
((to_date(SUBSTR(ta."end",0,(INSTR(ta."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24) 
- 
((to_date(SUBSTR(ta."start",0,(INSTR(ta."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24)
)),60)) AS minSek,
ta."name" AS name2
FROM view_task ta, view_instance vi
WHERE vi.definitionName = 'PROZESSDEFINITIONSNAME'
AND vi.id = ta.instanceId
AND ta."end" IS NOT NULL 
GROUP BY ta."name"​
),
(
SELECT FLOOR(MIN((
((to_date(SUBSTR(ta."end",0,(INSTR(ta."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24) 
- 
((to_date(SUBSTR(ta."start",0,(INSTR(ta."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24)
)/3600)) AS minH,
ta."name" AS name3
FROM view_task ta, view_instance vi
WHERE vi.definitionName = 'PROZESSDEFINITIONSNAME'
AND vi.id = ta.instanceId
AND ta."end" IS NOT NULL 
GROUP BY ta."name"​
),
(
SELECT FLOOR(MAX((
((to_date(SUBSTR(ta."end",0,(INSTR(ta."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24) 
- 
((to_date(SUBSTR(ta."start",0,(INSTR(ta."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24)
))) AS maxSekunden,
ta."name" AS name4
FROM view_task ta, view_instance vi
WHERE vi.definitionName = 'PROZESSDEFINITIONSNAME'
AND vi.id = ta.instanceId
AND ta."end" IS NOT NULL 
GROUP BY ta."name"​
),
(
SELECT FLOOR(MOD(MAX((
((to_date(SUBSTR(ta."end",0,(INSTR(ta."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24) 
- 
((to_date(SUBSTR(ta."start",0,(INSTR(ta."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24)
)),60)) AS maxSek,
ta."name" AS name5
FROM view_task ta, view_instance vi
WHERE vi.definitionName = 'PROZESSDEFINITIONSNAME'
AND vi.id = ta.instanceId
AND ta."end" IS NOT NULL 
GROUP BY ta."name"​
),
(
SELECT FLOOR(MAX((
((to_date(SUBSTR(ta."end",0,(INSTR(ta."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24) 
-
((to_date(SUBSTR(ta."start",0,(INSTR(ta."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24)
)/3600)) AS maxH,
ta."name" AS name6
FROM view_task ta, view_instance vi
WHERE vi.definitionName = 'PROZESSDEFINITIONSNAME'
AND vi.id = ta.instanceId
AND ta."end" IS NOT NULL 
GROUP BY ta."name"​
),
(
SELECT FLOOR(avg((
((to_date(SUBSTR(ta."end",0,(INSTR(ta."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24) 
- 
((to_date(SUBSTR(ta."start",0,(INSTR(ta."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24)
))) AS avgSekunden,
ta."name" AS name7
FROM view_task ta, view_instance vi
WHERE vi.definitionName = 'PROZESSDEFINITIONSNAME'
AND vi.id = ta.instanceId
AND ta."end" IS NOT NULL 
GROUP BY ta."name"​
),
(
SELECT FLOOR(MOD(avg((
((to_date(SUBSTR(ta."end",0,(INSTR(ta."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24) 
- 
((to_date(SUBSTR(ta."start",0,(INSTR(ta."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24)
)),60)) AS avgSek,
ta."name" AS name8
FROM view_task ta, view_instance vi
WHERE vi.definitionName = 'PROZESSDEFINITIONSNAME'
AND vi.id = ta.instanceId
AND ta."end" IS NOT NULL 
GROUP BY ta."name"​
),
(
SELECT FLOOR(avg((
((to_date(SUBSTR(ta."end",0,(INSTR(ta."end", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24) 
- 
((to_date(SUBSTR(ta."start",0,(INSTR(ta."start", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')  - DATE '1970-01-01' ) * 60 * 60 * 24)
))/3600) AS avgH,
ta."name" AS name9
FROM view_task ta, view_instance vi
WHERE vi.definitionName = 'PROZESSDEFINITIONSNAME'
AND vi.id = ta.instanceId
AND ta."end" IS NOT NULL 
GROUP BY ta."name"​
)
WHERE name1 = name2​
AND name1 = name3
AND name1 = name4
AND name1 = name5
AND name1 = name6
AND name1 = name7
AND name1 = name8
AND name1 = name9
)

Average, minimum, and maximum lead time for all tasks of a specified process definition (in h)

SELECT 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 "Average lead time",
CAST((MIN((((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)) "Minimum lead time",
CAST((MAX((((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 "Maximum lead time"
FROM view_task task
INNER JOIN
view_instance inst ON task.instanceId=inst.id
WHERE task."end" 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 inst.definitionname = 'NAME DER PROZESSDEFINITION'
GROUP BY task."name"
​

Average, minimum, and maximum lead time for all tasks of a specified process definition (in h) per month

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,
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 "Average lead time",
CAST((MIN((((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)) "Minimum lead time",
CAST((MAX((((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 "Maximum lead time"
FROM view_task task
INNER JOIN
view_instance inst ON task.instanceId=inst.id
WHERE task."end" 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 inst.definitionname = 'PROZESSDEFINITIONSNAME'
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'))
en/software/dashboard/analyses/task_analyses_oracle.txt · Last modified: 2021/07/01 09:52 (external edit)

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: 2013 T!M Solutions GmbH
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki