TIM Wiki / NEW TIM 6 Documentation

Benutzer-Werkzeuge

Webseiten-Werkzeuge

Sie befinden sich hier: start » software » dashboard » analyses » activity_analyses
Zuletzt angesehen: • activity_analyses
Plugin installed incorrectly. Rename plugin directory 'swiftmail.backup' to 'swiftmail'.
software:dashboard:analyses:activity_analyses

Inhaltsverzeichnis

Aktivitätenbezogene Auswertungen

Anzahl an erstellten, beendeten und offenen Aktivitäten je Prozessdefinition

SELECT inst.definitionname AS Prozess,
COUNT(DISTINCT(act1.id)) AS "Anzahl erstellte Aktivitäten",
COUNT(DISTINCT(act2.id)) AS "Anzahl beendete 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")
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
GROUP BY Prozess

Anzahl an erstellten, beendeten und offenen Aktivitäten einer bestimmten Prozessdefinition

SELECT defName AS Prozess,
COUNT(ended) AS "Anzahl erstellte Aktivitäten",
SUM(ended) AS "Anzahl beendete Aktivitäten", (SELECT COUNT(DISTINCT(task.id))
FROM view_task task INNER JOIN view_instance inst ON task.instanceid = inst.id
INNER JOIN view_activity act ON act.instanceid = inst.id
WHERE task.isopen IS TRUE AND act.END IS NULL AND inst.END IS NULL AND inst.archiv IS FALSE
AND inst.definitionname = defName) AS "Anzahl offene Aktivitäten" FROM
(SELECT inst.definitionname AS defname, IF(act.END IS NULL,0,1) AS ended FROM view_activity act
INNER JOIN view_instance inst ON inst.id=act.instanceid
WHERE TYPE IN ("K","S") AND START IS NOT NULL AND inst.definitionname= "Name der Prozessdefinition") AS subQu

Durchschnittliche Zeit (in hh:mm:ss) von Erstellung bis Erledigung von allen Aktivitäten (egal von welcher Prozessdefinition und auch außerhalb der Arbeitszeit)

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 "Durchschnittliche Liegezeit von Aktivitäten" 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_activity 
WHERE START IS NOT NULL AND END IS NOT NULL AND TYPE IN ("K","S")) AS dusub

Durchschnittliche Zeit (in hh:mm:ss) von Erstellung bis Erledigung von allen Aktivitäten je Monat (egal von welcher Prozessdefinition und auch außerhalb der Arbeitszeit) je Monat

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 "Durchschnittliche Liegezeit von Aktivitäten" 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_activity WHERE END IS NOT NULL
  AND START IS NOT NULL AND TYPE IN ("K","S") GROUP BY Monat ORDER BY YEAR(START) DESC, MONTH(START) DESC) AS dusub

Durchschnittliche Zeit (in hh:mm:ss) von Erstellung bis Erledigung von allen Aktivitäten je Monat ohne Ausreißer (von allen Prozessdefinitionen und auch außerhalb der Arbeitszeit)

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 "Durchschnittliche Liegezeit von Aktivitäten" 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_activity
WHERE START IS NOT NULL AND END IS NOT NULL AND TYPE IN ("K","S") AND CAST(FLOOR((unix_timestamp(END) - unix_timestamp(START))) AS DECIMAL(10,10)) < 10000 GROUP BY YEAR(START) DESC, MONTH(START) DESC) AS dusub

Durchschnittliche, minimale und maximale Durchlaufszeit aller Aktivitäten einer bestimmten Prozessdefinition (in hh:mm:ss)

SELECT  name AS Aktivität,
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 "Durchschnittliche Durchlaufzeit",
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 "Minimale Durchlaufzeit",
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 "Maximale Durchlaufzeit"
 
FROM
(SELECT activity.name AS name, definitionname AS defName,
FLOOR(MIN((unix_timestamp(activity.END) - unix_timestamp(activity.START)))) AS minSekunden,
FLOOR(MIN((unix_timestamp(activity.END) - unix_timestamp(activity.START)))%60) AS minSek,
FLOOR(MIN((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS minH,
FLOOR(MAX((unix_timestamp(activity.END) - unix_timestamp(activity.START)))) AS maxSekunden,
FLOOR(MAX((unix_timestamp(activity.END) - unix_timestamp(activity.START)))%60) AS maxSek,
FLOOR(MAX((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS maxH,
FLOOR(avg((unix_timestamp(activity.END) - unix_timestamp(activity.START)))) AS avgSekunden,
FLOOR(avg((unix_timestamp(activity.END) - unix_timestamp(activity.START)))%60) AS avgSek,
FLOOR(avg((unix_timestamp(activity.END) - unix_timestamp(activity.START)))/3600) AS avgH,
(unix_timestamp(activity.END)-unix_timestamp(activity.START)) AS datediff
FROM view_activity activity INNER JOIN view_instance inst ON activity.instanceId=inst.id
WHERE activity.END IS NOT NULL AND activity.START IS NOT NULL AND activity.TYPE IN ("K","S") AND definitionname = "Name der Prozessdefinition"
GROUP BY activity.name
) AS datediffsub
WHERE datediff > 0
GROUP BY name

Durchschnittliche, minimale und maximale Durchlaufszeit aller Aktivitäten einer bestimmten Prozessdefinition (in h)

SELECT  activity.name AS Aktivität,
CAST((MIN((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS DECIMAL(10,1)) AS "Minimale Durchlaufzeit",
CAST((AVG((unix_timestamp(activity.END) - unix_timestamp(activity.START)))/3600) AS DECIMAL(10,1)) AS "Durchschnittliche Durchlaufzeit",
CAST((MAX((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS DECIMAL(10,1)) AS "Maximale Durchlaufzeit"
FROM
view_activity activity INNER JOIN view_instance inst ON activity.instanceId=inst.id AND activity.TYPE IN ("K","S") AND
activity.END IS NOT NULL AND activity.START IS NOT NULL WHERE (unix_timestamp(activity.END)-unix_timestamp(activity.START)) > 0
AND definitionname = "Name der Prozessdefinition"
GROUP BY activity.name

Durchschnittliche, minimale und maximale Durchlaufszeit aller Aktivitäten einer bestimmten Prozessdefinition (in h) je Monat

SELECT concat(SUBSTRING(monthname(activity.START),1,3)," ",SUBSTRING(CAST(YEAR(activity.START) AS CHAR),3,2)) AS Monat, activity.name AS Aktivität,
CAST((MIN((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS DECIMAL(10,1)) AS "Minimale Durchlaufzeit",
CAST((AVG((unix_timestamp(activity.END) - unix_timestamp(activity.START)))/3600) AS DECIMAL(10,1)) AS "Durchschnittliche Durchlaufzeit",
CAST((MAX((unix_timestamp(activity.END) - unix_timestamp(activity.START))/3600)) AS DECIMAL(10,1)) AS "Maximale Durchlaufzeit"
FROM
view_activity activity INNER JOIN view_instance inst ON activity.instanceId=inst.id AND activity.TYPE IN ("K","S") AND
activity.END IS NOT NULL AND activity.START IS NOT NULL WHERE (unix_timestamp(activity.END)-unix_timestamp(activity.START)) > 0
AND definitionname = "Name der Prozessdefinition"
GROUP BY YEAR(activity.START), MONTH(activity.START) DESC, activity.name
software/dashboard/analyses/activity_analyses.txt · Zuletzt geändert: 2021/07/01 09:52 (Externe Bearbeitung)

Seiten-Werkzeuge

Falls nicht anders bezeichnet, ist der Inhalt dieses Wikis unter der folgenden Lizenz veröffentlicht: 2013 T!M Solutions GmbH
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki