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

=====Task-Related Analyses=====
==== All open tasks for a specific user ====
<code sql>
select i.lastname as Nachname, i.firstname as Vorname, i.name as Username, t.name as Task, pi.name as Instanzname, pi.definitionName as Prozessname 
from view_activity a, view_task t, view_identity i, view_instance pi 
where a.id = t.activity and t.actor = i.id and a.end is null and t.end is null and pi.id = a.instanceId and pi.archiv = 0 and pi.end is null and i.lastname = "Barth"
</code>

==== Number of created, completed, and open tasks per process definition ====
<code sql>
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 is false
GROUP BY inst.definitionname
</code>
==== Number of created, completed, and open tasks for a specified process definition ====
<code sql>
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 is false
where inst.definitionname ="Name of process definition"
</code>
==== Average time (in hh:mm:ss) from creation to completion for all tasks (independent of which process definition and also outside of working hours) ====
<code sql>
SELECT IF((FLOOR(sekunden))<60,
  concat(IF((FLOOR(sekunden))<10,"00:00:0","00:00:"),(FLOOR(sekunden))),
  IF((FLOOR(sekunden))>3600,
    CAST(concat(h,IF(FLOOR((FLOOR(sekunden)- h*3600-sek)/60)<10,":0",":"),FLOOR((FLOOR(sekunden)- h*3600-sek)/60),IF(sek<10,":0",":"),sek) AS CHAR),
    concat(IF(FLOOR(sekunden/60)<10,"00:0","00:"),FLOOR(sekunden/60),IF(FLOOR(sek)<10,":0",":"),FLOOR(sek))
  )
) AS "Average wait time for tasks" FROM (SELECT FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))) AS sekunden,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))%60) AS sek,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))/3600) AS h
FROM view_task WHERE end is not null) AS dusub
</code>

==== Average time (in hh:mm:ss) from creation to completion for all tasks per month (independent of which process definition and also outside of working hours) ====
<code sql>
SELECT Monat, IF((FLOOR(sekunden))<60,
  concat(IF((FLOOR(sekunden))<10,"00:00:0","00:00:"),(FLOOR(sekunden))),
  IF((FLOOR(sekunden))>3600,
    CAST(concat(h,IF(FLOOR((FLOOR(sekunden)- h*3600-sek)/60)<10,":0",":"),FLOOR((FLOOR(sekunden)- h*3600-sek)/60),IF(sek<10,":0",":"),sek) AS CHAR),
    concat(IF(FLOOR(sekunden/60)<10,"00:0","00:"),FLOOR(sekunden/60),IF(FLOOR(sek)<10,":0",":"),FLOOR(sek))
  )
) AS "Average wait time for tasks" FROM
(SELECT concat(SUBSTRING(monthname(START),1,3)," ",SUBSTRING(CAST(YEAR(START) AS CHAR),3,2)) AS Monat,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))) AS sekunden,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))%60) AS sek,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))/3600) AS h FROM view_task 
WHERE isOpen = "0" GROUP BY YEAR(START), MONTH(START)) AS dusub
</code>

==== Average time (in hh:mm:ss) from creation to completion for all tasks excluding outliers (independent of which process definition and also outside of working hours) ====
<code sql>
SELECT Monat, IF((FLOOR(sekunden))<60,
  concat(if((FLOOR(sekunden))<10,"00:00:0","00:00:"),(FLOOR(sekunden))),
  IF((FLOOR(sekunden))>3600,
    cast(concat(h,if(FLOOR((FLOOR(sekunden)- h*3600-sek)/60)<10,":0",":"),FLOOR((FLOOR(sekunden)- h*3600-sek)/60),if(sek<10,":0",":"),sek) as CHAR),
    concat(if(FLOOR(sekunden/60)<10,"00:0","00:"),FLOOR(sekunden/60),if(FLOOR(sek)<10,":0",":"),FLOOR(sek))
  )
) AS "Average wait time for tasks" FROM
(select concat(SUBSTRING(monthname(START),1,3)," ",SUBSTRING(CAST(YEAR(START) AS CHAR),3,2)) AS Monat,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))) as sekunden,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))%60) as sek,
FLOOR(avg((unix_timestamp(END) - unix_timestamp(START)))/3600) as h FROM view_task
WHERE isOpen = "0" AND CAST(FLOOR((unix_timestamp(END) - unix_timestamp(START))) AS DECIMAL(10,10)) < 10000 group by YEAR(START), MONTH(START)) as dusub 
</code>

==== All open task with an active instance (not ended and not archived) and active activity (not ended and not archived) with user ====
<code sql>
SELECT tsk.name AS Aufgabe, IF (concat(ident.lastname, ", ",ident.firstname) IS NOT NULL,
concat(ident.lastname, ", ",ident.firstname), grouped.name) AS 'Bearbeiter',
inst.name AS 'Instanz', 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 = 'false' AND tsk.isOpen = 1
</code>


==== Average, minimum, and maximum lead time for all tasks in a specified process definition (in hh:mm:ss) ====
<code sql>
SELECT name AS Aufgabe,
IF((FLOOR(avgSekunden))<60,
  concat(IF((FLOOR(avgSekunden))<10,"00:00:0","00:00:"),(FLOOR(avgSekunden))),
  IF((FLOOR(avgSekunden))>3600,
    CAST(concat(avgH,IF(FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60)<10,":0",":"),FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60),IF(avgSek<10,":0",":"),avgSek) AS CHAR),
    concat(IF(FLOOR(avgSekunden/60)<10,"00:0","00:"),FLOOR(avgSekunden/60),IF(FLOOR(avgSek)<10,":0",":"),FLOOR(avgSek))
  )
)AS "Average lead time",
IF((FLOOR(minSekunden))<60,
  concat(IF((FLOOR(minSekunden))<10,"00:00:0","00:00:"),(FLOOR(minSekunden))),
  IF((FLOOR(minSekunden))>3600,
    CAST(concat(minH,IF(FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60)<10,":0",":"),FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60),IF(minSek<10,":0",":"),minSek) AS CHAR),
    concat(IF(FLOOR(minSekunden/60)<10,"00:0","00:"),FLOOR(minSekunden/60),IF(FLOOR(minSek)<10,":0",":"),FLOOR(minSek))
  )
) AS "Minimum lead time",
IF((FLOOR(maxSekunden))<60,
  concat(IF((FLOOR(maxSekunden))<10,"00:00:0","00:00:"),(FLOOR(maxSekunden))),
  IF((FLOOR(maxSekunden))>3600,
    CAST(concat(maxH,IF(FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60)<10,":0",":"),FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60),IF(maxSek<10,":0",":"),maxSek) AS CHAR),
    concat(IF(FLOOR(maxSekunden/60)<10,"00:0","00:"),FLOOR(maxSekunden/60),IF(FLOOR(maxSek)<10,":0",":"),FLOOR(maxSek))
  )
) AS "Maximum lead time"
FROM
(SELECT inst.definitionname AS defName, task.name AS name,
FLOOR(MIN((unix_timestamp(task.END) - unix_timestamp(task.START)))) AS minSekunden,
FLOOR(MIN((unix_timestamp(task.END) - unix_timestamp(task.START)))%60) AS minSek,
FLOOR(MIN((unix_timestamp(task.END) - unix_timestamp(task.START))/3600)) AS minH,
FLOOR(MAX((unix_timestamp(task.END) - unix_timestamp(task.START)))) AS maxSekunden,
FLOOR(MAX((unix_timestamp(task.END) - unix_timestamp(task.START)))%60) AS maxSek,
FLOOR(MAX((unix_timestamp(task.END) - unix_timestamp(task.START))/3600)) AS maxH,
FLOOR(avg((unix_timestamp(task.END) - unix_timestamp(task.START)))) AS avgSekunden,
FLOOR(avg((unix_timestamp(task.END) - unix_timestamp(task.START)))%60) AS avgSek,
FLOOR(avg((unix_timestamp(task.END) - unix_timestamp(task.START))/3600)) AS avgH,
(unix_timestamp(task.END)-unix_timestamp(task.START)) AS datediff
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 inst.definitionname = "Name of process definition"
GROUP BY task.name
) AS datediffsub
WHERE datediff > 0
GROUP BY name
</code>

==== Average, minimum, and maximum lead time for all tasks in a specified process definition (in h) ====
<code sql>
SELECT task.name AS Aufgabe,
CAST((MIN((unix_timestamp(task.END) - unix_timestamp(task.START))/3600)) AS DECIMAL(10,1)) AS "Minimum lead time",
CAST((avg((unix_timestamp(task.END) - unix_timestamp(task.START))/3600)) AS DECIMAL(10,1)) AS "Average lead time",
CAST((MAX((unix_timestamp(task.END) - unix_timestamp(task.START))/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 (unix_timestamp(task.END)-unix_timestamp(task.START)) >0
and inst.definitionname = "Name of process definition"
GROUP BY task.name
</code>

====Average, minimum, and maximum lead time for all tasks in a specified process definition (in h) per month ====
<code sql>
SELECT concat(SUBSTRING(monthname(task.START),1,3)," ",SUBSTRING(CAST(YEAR(task.START) AS CHAR),3,2)) AS Monat, task.name AS Aufgabe,
  CAST(avg((unix_timestamp(task.END) - unix_timestamp(task.START))/3600)  AS DECIMAL(10,1)) AS "Average lead time",
CAST(MIN((unix_timestamp(task.END) - unix_timestamp(task.START))/3600) AS DECIMAL(10,1)) AS "Minimum lead time",
CAST(MAX((unix_timestamp(task.END) - unix_timestamp(task.START))/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 (unix_timestamp(task.END) - unix_timestamp(task.START)) >0
AND inst.definitionname = "Name of process definition"
GROUP BY YEAR(task.START) DESC, MONTH(task.START) DESC, task.name
</code>