=====Instanzbezogene Auswertungen=====
==== Anzahl an gestarteten Instanzen pro Monat (unabhängig vom Prozess) ====
select concat(substring(cast(year(inst.creationtime) as char),3,2), " ", MONTH(inst.creationtime)) as monat, count(*) as "Summe gestartete Instanzen"
from view_instance inst
group by monat
==== Anzahl an gestarteten Instanzen pro Jahr (unabhängig vom Prozess) ====
select year(inst.creationtime) as jahr, count(*) as "Summe gestartete Instanzen"
from view_instance inst
group by jahr
==== Anzahl an gestarteten und beendeten Instanzen je Definition ====
select inst1.definitionname as Prozess, count(distinct(inst1.id)) as "Anzahl gestarteter Instanzen", count(distinct(inst2.id)) as "Anzahl beendeter Instanzen", count(distinct(task.id)) as "Anzahl offener Aufgaben"
from view_instance inst1 left join view_instance inst2 on inst1.id=inst2.id and inst2.end is not null
inner join view_activity act on act.instanceid=inst1.id
left join view_task task on task.instanceid=inst1.id
and task.end is null and act.start is not null and act.end is null and inst1.end is null and inst1.archiv is false
group by inst1.definitionname
==== Anzahl an gestarteten und beendeten Instanzen sowie die Anzahl an offenen Aufgaben einer Prozessdefinition ====
select inst1.definitionname as Prozess, count(inst1.id) as "Anzahl gestarteter Instanzen", count(inst2.id) as "Anzahl beendeter Instanzen", (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 = Prozess) AS "Anzahl offene Aufgaben"
from view_instance inst1 left join view_instance inst2 on inst1.id=inst2.id and inst2.end is not null
where inst1.definitionname = "Name der Prozessdefinition"
==== Anzahl an gestarteten, beendeten, abgebrochenen und laufenden Instanzen je Prozessdefinition ====
select definitionname as Definitionsname,
sum(alle) as "Gestartete Instanzen",
sum(beendet) as "Beendete Instanzen (mit Ende)",
sum(abgebrochen) as "Abgebrochene Instanzen (Archiviert ohne Ende)",
sum(laufend) as "Laufende Instanzen"
from (Select if(archiv is true,
if(end is null,1,0),0) as abgebrochen,
if(end is not null,1,0) as beendet,
if(end is null,if(archiv is true,0,1),0) as laufend,
1 as alle, definitionname
from view_instance) as subqu group by definitionname
==== Finanzielle Aufwände je Instanz ====
select inst.name as Instanzname,SUM(Cast(eff.value as Signed)) as Finanzaufwände
from view_effort eff
inner join view_instance inst on eff.instanceID=inst.id
where eff.effortType="FINANCE"
group by inst.id
==== Anzahl der Schleifen (>0) je Instanz ====
SELECT inst.DEFINITIONNAME AS Prozess, inst.name AS Instanz, SUM(IF(act.loopcount IS NOT NULL,1,0 )) AS Schleifendurchläufe
FROM view_instance inst, view_activity act
WHERE act.instanceID = inst.id
GROUP BY inst.id, inst.definitionname, inst.name ORDER BY inst.definitionname
==== Aktuelle Aktivität(en) und Bearbeiter aller offenen Instanzen ====
SELECT
inst.name AS Prozessinstanz,
inst.definitionName AS Prozessdefinition,
group_concat(act.name SEPARATOR ', ') AS Aktivität,
group_concat(task.name SEPARATOR ', ') as Aufgaben,
group_concat(IF(ident.id IS NULL,ident2.name, concat(ident.firstname, " ",ident.lastname, " (",ident2.name,")")), '') as Username
FROM view_instance inst
INNER JOIN view_activity act ON act.instanceid = inst.id AND act.START IS NOT NULL AND act.END IS NULL AND act.TYPE = 'K'
INNER JOIN view_task task ON task.activity = act.id
LEFT JOIN view_identity ident ON task.actor = ident.id
LEFT JOIN view_identity ident2 ON task.pooledActor = ident2.id
WHERE inst.archiv = FALSE AND inst.END IS NULL
GROUP BY inst.id
==== Alle offenen Instanzen mit einer bestimmten Zeichenfolge im Instanznamen ====
SELECT DISTINCT vi."name" AS "Instanzname", vi."DEFINITIONNAME" AS "Prozessname"
FROM view_activity a, view_instance vi
WHERE a."end" is null
and a."start" IS NOT NULL
AND vi.id = a.instanceId
AND vi.archiv = 0
and vi."END" is null
and vi."name" like '%tester%'
==== Durchschnittliche Durchlaufszeit (in hh:mm:ss) von allen Instanzen aller Prozessdefinitionen 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 Durchlaufzeit" from
(SELECT FLOOR(avg(TIMESTAMPDIFF(SECOND,creationtime,END))) AS sekunden,
FLOOR(avg(TIMESTAMPDIFF(SECOND,creationtime,END))%60) AS sek,
FLOOR(avg(TIMESTAMPDIFF(SECOND,creationtime,END))/3600) AS h,
concat(SUBSTRING(monthname(creationtime),1,3)," ",SUBSTRING(CAST(YEAR(creationtime) AS CHAR),3,2)) as Monat
FROM view_instance WHERE end is not null group by Monat order by YEAR(creationtime), MONTH(creationtime) ) AS dusub
==== Durchschnittliche Durchlaufszeit (in h) von allen Instanzen aller Prozessdefinitionen je Monat ====
SELECT Monat, h
AS "Durchschnittliche Durchlaufzeit in Stunden" FROM
(Select CAST((avg(TIMESTAMPDIFF(SECOND,creationtime,END))/3600) as DECIMAL(10,1)) AS h,
concat(SUBSTRING(monthname(creationtime),1,3)," ",SUBSTRING(CAST(YEAR(creationtime) AS CHAR),3,2)) AS Monat
FROM view_instance WHERE END IS NOT NULL GROUP BY Monat ORDER BY YEAR(creationtime), MONTH(creationtime) ) AS dusub
==== Durchschnittliche Durchlaufszeit (in h) von allen Instanzen einer Prozessdefinition je Monat ====
SELECT Monat, h
AS "Durchschnittliche Durchlaufzeit in Stunden" FROM
(Select CAST((avg(TIMESTAMPDIFF(SECOND,creationtime,END))/3600) as DECIMAL(10,1)) AS h,
concat(SUBSTRING(monthname(creationtime),1,3)," ",SUBSTRING(CAST(YEAR(creationtime) AS CHAR),3,2)) AS Monat
FROM view_instance WHERE END IS NOT NULL and definitionname = "Name der Definition" GROUP BY Monat ORDER BY YEAR(creationtime), MONTH(creationtime) ) AS dusub
==== Instanzen je Aktivität einer Prozessdefinition (analog zur Auswertung in den PM-Clients)===
SELECT SUBSTRING_INDEX( va.name, '(L:', 1 ) AS "Aktivitäten", COUNT(SUBSTRING_INDEX( va.name, '(L:', 1 )) AS Anzahl
FROM view_activity va JOIN view_instance vi ON vi.id = va.instanceId
WHERE va.end IS NULL
AND (va.type = "K" OR va.type = "C")
AND va.start IS NOT NULL
AND vi.definitionName LIKE "PROZESSNAME"
AND vi.archiv = 0
GROUP BY SUBSTRING_INDEX(va.name, '(L:', 1 )
==== Anzahl der gestarteten und beendeten Instanzen je Monat und Definition ====
Select Monat, Prozess, gestartete as "Anzahl gestartete Instanzen", beendete as "Anzahl beendete Instanzen"
from
(select count(distinct(inst1.id)) as gestartete,
concat(SUBSTRING(monthname(inst1.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst1.creationtime) AS CHAR),3,2)) as Monat, inst1.definitionname as Prozess, count(distinct(inst2.id)) as beendete, year(inst1.creationtime) as jahr, month(inst1.creationtime) as monat2
from view_instance inst1 left outer join view_instance inst2 on concat(SUBSTRING(monthname(inst1.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst1.creationtime) AS CHAR),3,2))=concat(SUBSTRING(monthname(inst2.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst2.end) AS CHAR),3,2)) and inst1.definitionname=inst2.definitionname
group by Monat, Prozess
union
select count(distinct(inst1.id)) as gestartete,
concat(SUBSTRING(monthname(inst2.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst2.end) AS CHAR),3,2)) as Monat, inst2.definitionname as Prozess, count(distinct(inst2.id)) as beendete, year(inst2.end) as jahr, month(inst2.end) as monat2
from view_instance inst1 right outer join view_instance inst2 on concat(SUBSTRING(monthname(inst1.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst1.creationtime) AS CHAR),3,2))=concat(SUBSTRING(monthname(inst2.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst2.end) AS CHAR),3,2)) and inst1.definitionname=inst2.definitionname
where inst2.end is not null group by Monat, Prozess) as spalten
order by jahr, monat2, Prozess
==== Anzahl der gestarteten und beendeten Instanzen je Monat für eine Definition ====
Select Monat, Prozess, gestartete as "Anzahl gestartete Instanzen", beendete as "Anzahl beendete Instanzen"
from
(select count(distinct(inst1.id)) as gestartete,
concat(SUBSTRING(monthname(inst1.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst1.creationtime) AS CHAR),3,2)) as Monat, inst1.definitionname as Prozess, count(distinct(inst2.id)) as beendete, year(inst1.creationtime) as jahr, month(inst1.creationtime) as monat2
from view_instance inst1 left outer join view_instance inst2 on concat(SUBSTRING(monthname(inst1.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst1.creationtime) AS CHAR),3,2))=concat(SUBSTRING(monthname(inst2.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst2.end) AS CHAR),3,2)) and inst1.definitionname=inst2.definitionname
where inst1.definitionname = "Name der Definition"
group by Monat, Prozess
union
select count(distinct(inst1.id)) as gestartete,
concat(SUBSTRING(monthname(inst2.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst2.end) AS CHAR),3,2)) as Monat, inst2.definitionname as Prozess, count(distinct(inst2.id)) as beendete, year(inst2.end) as jahr, month(inst2.end) as monat2
from view_instance inst1 right outer join view_instance inst2 on concat(SUBSTRING(monthname(inst1.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst1.creationtime) AS CHAR),3,2))=concat(SUBSTRING(monthname(inst2.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst2.end) AS CHAR),3,2)) and inst1.definitionname=inst2.definitionname
where inst2.end is not null and inst1.definitionname = "Name der Definition" group by Monat, Prozess) as spalten
order by jahr, monat2, Prozess
==== Anzahl der gestarteten Instanzen je Monat (Spalten) je Jahr (Zeilen) für eine Definition ====
select
Jahr,
sum(if(Monat='January',1,0)) as Januar,
sum(if(Monat='February',1,0)) as Februar,
sum(if(Monat='March',1,0)) as März,
sum(if(Monat='April',1,0)) as April,
sum(if(Monat='May',1,0)) as Mai,
sum(if(Monat='June',1,0)) as Juni,
sum(if(Monat='July',1,0)) as July,
sum(if(Monat='August',1,0)) as August,
sum(if(Monat='September',1,0)) as September,
sum(if(Monat='October',1,0)) as Oktober,
sum(if(Monat='November',1,0)) as November,
sum(if(Monat='December',1,0)) as Dezember
from
(select monthname(creationtime) as Monat,year(creationtime) as Jahr
from view_instance
where definitionname = 'Definitionsname'
) as Base
group by Jahr
====Anzahl der gestarteten Instanzen je Monat (Zeilen) je Jahr (Spalten) für eine Definition =====
Select Monat,
sum(if(Jahr='2010',1,0)) as '2010',
sum(if(Jahr='2011',1,0)) as '2011',
sum(if(Jahr='2012',1,0)) as '2012',
sum(if(Jahr='2013',1,0)) as '2013',
sum(if(Jahr='2014',1,0)) as '2014'
from
(select monthname(creationtime) as Monat,year(creationtime) as Jahr, creationtime as creationtime
from view_instance
where definitionname = 'NAME DER DEFINITION'
) as Base
group by Monat order by month(creationtime)
==== Kumulierte Anzahl der gestarteten Instanzen je Monat (Spalten) je Jahr (Zeilen) für eine Definition ====
select
Jahr,
sum(if(Monat='January',1,0)) as Januar,
sum(if(Monat='February' or Monat='January',1,0)) as Februar,
sum(if(Monat='March' or Monat='January' or Monat='February',1,0)) as März,
sum(if(Monat='April' or Monat='January' or Monat='February' or Monat='March',1,0)) as April,
sum(if(Monat='May' or Monat='January' or Monat='February' or Monat='March' or Monat='April',1,0)) as Mai,
sum(if(Monat='June' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June',1,0)) as Juni,
sum(if(Monat='July' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June',1,0)) as Juli,
sum(if(Monat='August' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June' or Monat='July',1,0)) as August,
sum(if(Monat='September' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June' or Monat='July' or Monat='August',1,0)) as September,
sum(if(Monat='October' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June' or Monat='July' or Monat='August' or Monat='September',1,0)) as Oktober,
sum(if(Monat='November' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June' or Monat='July' or Monat='August' or Monat='September' or Monat='October',1,0)) as November,
sum(if(Monat='December' or Monat='January' or Monat='February' or Monat='March' or Monat='April' or Monat='May' or Monat='June' or Monat='July' or Monat='August' or Monat='September' or Monat='October' or Monat='November',1,0)) as Dezember
from
(select monthname(creationtime) as Monat,year(creationtime) as Jahr
from view_instance
where definitionname = 'Definitionsname'
) as Base
group by Jahr
====Kumulierte Anzahl der gestarteten Instanzen je Monat (Zeilen) je Jahr (Spalten) für eine Definition =====
SELECT t.Monat,
(SELECT SUM(w.2011)
FROM (Select Monat,Monatszahl,
sum(if(Jahr='2011',1,0)) as '2011'
from
(select month(creationtime) as Monatszahl, monthname(creationtime) as Monat,year(creationtime) as Jahr
from view_instance
where definitionname = 'NAME DER DEFINITION'
) as Base
group by Monat order by Monatszahl) as w
WHERE w.Monatszahl<= t.Monatszahl) AS 'Summe 2011',
(SELECT SUM(x.2012)
FROM (Select Monat,Monatszahl,
sum(if(Jahr='2012',1,0)) as '2012'
from
(select month(creationtime) as Monatszahl, monthname(creationtime) as Monat,year(creationtime) as Jahr
from view_instance
where definitionname = 'NAME DER DEFINITION'
) as Base
group by Monat order by Monatszahl) as x
WHERE x.Monatszahl<= t.Monatszahl) AS 'Summe 2012',a
(SELECT SUM(y.2013)
FROM (Select Monat,Monatszahl,
sum(if(Jahr='2013',1,0)) as '2013'
from
(select month(creationtime) as Monatszahl, monthname(creationtime) as Monat,year(creationtime) as Jahr
from view_instance
where definitionname = 'NAME DER DEFINITION'
) as Base
group by Monat order by Monatszahl) as y
WHERE y.Monatszahl<= t.Monatszahl) AS 'Summe 2013',
(SELECT SUM(z.2014)
FROM (Select Monat,Monatszahl,
sum(if(Jahr='2014',1,0)) as '2014'
from
(select month(creationtime) as Monatszahl, monthname(creationtime) as Monat,year(creationtime) as Jahr
from view_instance
where definitionname = 'NAME DER DEFINITION'
) as Base
group by Monat order by Monatszahl) as z
WHERE z.Monatszahl<= t.Monatszahl) AS 'Summe 2014'
FROM (Select Monat,Monatszahl,
sum(if(Jahr='2012',1,0)) as '2012',
sum(if(Jahr='2013',1,0)) as '2013',
sum(if(Jahr='2014',1,0)) as '2014'
from
(select month(creationtime) as Monatszahl, monthname(creationtime) as Monat,year(creationtime) as Jahr
from view_instance
where definitionname = 'NAME DER DEFINITION'
) as Base
group by Monat order by Monatszahl) as t
ORDER BY t.Monatszahl
==== Minimale, maximale und durchschnittliche Laufzeit (in hh:mm:ss) der Instanzen je Prozessdefinition ====
SELECT name AS Prozess,
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 Durchlaufszeit",
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 Durchlaufszeit",
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 Durchlaufszeit"
FROM
(SELECT definitionname AS name,
FLOOR(MIN((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))) AS minSekunden,
FLOOR(MIN((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))%60) AS minSek,
FLOOR(MIN((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime))/3600)) AS minH,
FLOOR(MAX((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))) AS maxSekunden,
FLOOR(MAX((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))%60) AS maxSek,
FLOOR(MAX((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime))/3600)) AS maxH,
FLOOR(avg((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))) AS avgSekunden,
FLOOR(avg((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))%60) AS avgSek,
FLOOR(avg((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))/3600) AS avgH,
(unix_timestamp(inst.END)-unix_timestamp(inst.creationtime)) AS datediff FROM view_instance inst
WHERE inst.END IS NOT NULL GROUP BY definitionname
) AS datediffsub
WHERE datediff > 0
GROUP BY name
==== Minimale, maximale und durchschnittliche Laufzeit (in h) der Instanzen je Prozessdefinition ====
SELECT definitionname AS Prozess,
CAST((avg((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))/3600) AS DECIMAL(10,1)) AS "Durschnittliche Durchlaufszeit",
CAST((MIN((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime))/3600)) AS DECIMAL(10,1)) "Minimale Durchlaufszeit",
CAST((MAX((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime))/3600)) AS DECIMAL(10,1)) AS "Maximale Durchlaufszeit"
FROM view_instance inst
WHERE inst.END IS NOT NULL AND (unix_timestamp(inst.END)-unix_timestamp(inst.creationtime)) > 0 GROUP BY definitionname
==== Durchschnittliche Durchlaufszeit (in hh:mm:ss) der Instanzen einer bestimmten Prozessdefinition 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 Durchlaufzeit" FROM
(SELECT FLOOR(avg(TIMESTAMPDIFF(SECOND,vi.creationtime,vi.END))) AS sekunden,
FLOOR(avg(TIMESTAMPDIFF(SECOND,vi.creationtime,vi.END))%60) AS sek,
FLOOR(avg(TIMESTAMPDIFF(SECOND,vi.creationtime,vi.END))/3600) AS h,
concat(SUBSTRING(monthname(vi.creationTime),1,3)," ",SUBSTRING(CAST(YEAR(vi.creationTime) AS CHAR),3,2)) AS Monat,
vi.creationtime as creationtime
FROM view_instance vi, view_definition vd
WHERE vd.id = vi.definitionId AND vi.END IS NOT NULL AND
vd.name = "Name der Prozessdefinition" GROUP BY YEAR(vi.creationtime), MONTH(vi.creationtime)) AS dusub
GROUP BY YEAR(creationtime), MONTH(creationtime)
==== Anzahl gestarteter Instanzen je Monat für eine Definition ====
SELECT concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat,
SUM(IF(inst.definitionname ="Name der Prozessdefinition",1,0)) AS "Anzahl"
FROM view_instance inst WHERE inst.definitionname = "Name der Prozessdefinition"
GROUP BY YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC
==== Anzahl gestarteter Instanzen je Monat für 2 Definitionen ====
select concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat,
sum(IF(inst.definitionname ="Name der 1. Definition",1,0)) AS "Definition 1 Count",
sum(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count'
from view_instance inst where inst.definitionname = "Name der 1. Definition"
OR inst.definitionname = "Name der 2. Definition"
group by YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC
==== Anzahl gestarteter Instanzen je Monat für 3 Definitionen ====
select concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat,
sum(IF(inst.definitionname ="Name der 1. Definition",1,0)) AS "Definition 1 Count",
sum(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count' ,
sum(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count'
from view_instance inst where inst.definitionname = "Name der 1. Definition"
OR inst.definitionname = "Name der 2. Definition"
OR inst.definitionname = "Name der 3. Definition"
group by YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC
==== Anzahl gestarteter Instanzen je Monat für 4 Definitionen ====
select concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat,
sum(IF(inst.definitionname ="Name der 1. Definition",1,0)) AS 'Definition 1 Count',
sum(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count',
sum(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count',
sum(IF(inst.definitionname = "Name der 4. Definition",1,0)) AS 'Definition 4 Count'
from view_instance inst where inst.definitionname = "Name der 1. Definition"
OR inst.definitionname = "Name der 2. Definition"
OR inst.definitionname = "Name der 3. Definition"
OR inst.definitionname = "Name der 4. Definition"
group by YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC
==== Anzahl gestarteter Instanzen je Monat für 5 Definitionen ====
SELECT concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat,
SUM(IF(inst.definitionname ="Name der 1. Definition",1,0)) AS 'Definition 1 Count',
SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count',
SUM(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count',
SUM(IF(inst.definitionname = "Name der 4. Definition",1,0)) AS 'Definition 4 Count',
SUM(IF(inst.definitionname = "Name der 5. Definition",1,0)) AS 'Definition 5 Count'
FROM view_instance inst WHERE inst.definitionname = "Name der 1. Definition"
OR inst.definitionname = "Name der 2. Definition"
OR inst.definitionname = "Name der 3. Definition"
OR inst.definitionname = "Name der 4. Definition"
OR inst.definitionname = "Name der 5. Definition"
GROUP BY YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC
==== Anzahl gestarteter Instanzen je Monat für 6 Definitionen ====
SELECT concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat,
SUM(IF(inst.definitionname ="Name der 1. Definition",1,0)) AS 'Definition 1 Count',
SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count',
SUM(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count',
SUM(IF(inst.definitionname = "Name der 4. Definition",1,0)) AS 'Definition 4 Count',
SUM(IF(inst.definitionname = "Name der 5. Definition",1,0)) AS 'Definition 5 Count',
SUM(IF(inst.definitionname = "Name der 6. Definition",1,0)) AS 'Definition 6 Count'
FROM view_instance inst WHERE inst.definitionname = "Name der 1. Definition"
OR inst.definitionname = "Name der 2. Definition"
OR inst.definitionname = "Name der 3. Definition"
OR inst.definitionname = "Name der 4. Definition"
OR inst.definitionname = "Name der 5. Definition"
OR inst.definitionname = "Name der 6. Definition"
GROUP BY YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC
==== Anzahl beendeter Instanzen je Monat für eine Definition ====
SELECT concat(SUBSTRING(monthname(inst.END),1,3), " " ,SUBSTRING(CAST(YEAR(inst.END) AS CHAR),3,2)) AS Monat,
SUM(IF(inst.definitionname ="Name der Definition", 1,0)) AS 'Anzahl'
FROM view_instance inst WHERE inst.END IS NOT NULL AND
inst.definitionname = "Name der Definition"
GROUP BY YEAR(inst.END) DESC, MONTH(inst.END) DESC
==== Anzahl beendeter Instanzen je Monat für 2 Definitionen ====
SELECT concat(SUBSTRING(monthname(inst.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst.end) AS CHAR),3,2)) AS Monat,
SUM(IF(inst.definitionname ="Name der 1. Definition", 1,0)) AS 'Definition 1 Count',
SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count'
FROM view_instance inst WHERE inst.end IS NOT NULL AND
(inst.definitionname = "Name der 1. Definition"
OR inst.definitionname = "Name der 2. Definition")
GROUP BY YEAR(inst.end) DESC, MONTH(inst.end) DESC
==== Anzahl beendeter Instanzen je Monat für 3 Definitionen ====
SELECT concat(SUBSTRING(monthname(inst.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst.end) AS CHAR),3,2)) AS Monat,
SUM(IF(inst.definitionname ="Name der 1. Definition",1,0)) AS 'Definition 1 Count',
SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count',
SUM(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count'
FROM view_instance inst WHERE inst.end IS NOT NULL AND
(inst.definitionname = "Name der 1. Definition"
OR inst.definitionname = "Name der 2. Definition"
OR inst.definitionname = "Name der 3. Definition")
GROUP BY YEAR(inst.end) DESC, MONTH(inst.end) DESC
==== Anzahl beendeter Instanzen je Monat für 4 Definitionen ====
SELECT concat(SUBSTRING(monthname(inst.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst.end) AS CHAR),3,2)) AS Monat,
SUM(IF(inst.definitionname ="Name der 1. Definition", 1,0)) AS 'Definition 1 Count',
SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count',
SUM(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count',
SUM(IF(inst.definitionname = "Name der 4. Definition",1,0)) AS 'Definition 4 Count'
FROM view_instance inst WHERE inst.end IS NOT NULL AND
(inst.definitionname = "Name der 1. Definition"
OR inst.definitionname = "Name der 2. Definition"
OR inst.definitionname = "Name der 3. Definition"
OR inst.definitionname = "Name der 4. Definition")
GROUP BY YEAR(inst.end) DESC, MONTH(inst.end) DESC
==== Anzahl beendeter Instanzen je Monat für 5 Definitionen ====
SELECT concat(SUBSTRING(monthname(inst.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst.end) AS CHAR),3,2)) AS Monat,
SUM(IF(inst.definitionname ="Name der 1. Definition", 1,0)) AS 'Definition 1 Count',
SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count',
SUM(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count',
SUM(IF(inst.definitionname = "Name der 4. Definition",1,0)) AS 'Definition 4 Count',
SUM(IF(inst.definitionname = "Name der 5. Definition",1,0)) AS 'Definition 5 Count'
FROM view_instance inst WHERE inst.end IS NOT NULL AND
(inst.definitionname = "Name der 1. Definition"
OR inst.definitionname = "Name der 2. Definition"
OR inst.definitionname = "Name der 3. Definition"
OR inst.definitionname = "Name der 4. Definition"
OR inst.definitionname = "Name der 5. Definition")
GROUP BY YEAR(inst.end) DESC, MONTH(inst.end) DESC
==== Anzahl beendeter Instanzen je Monat für 6 Definitionen ====
SELECT concat(SUBSTRING(monthname(inst.END),1,3), " " ,SUBSTRING(CAST(YEAR(inst.END) AS CHAR),3,2)) AS Monat,
SUM(IF(inst.definitionname ="Name der 1. Definition", 1,0)) AS 'Definition 1 Count',
SUM(IF(inst.definitionname = "Name der 2. Definition",1,0)) AS 'Definition 2 Count',
SUM(IF(inst.definitionname = "Name der 3. Definition",1,0)) AS 'Definition 3 Count',
SUM(IF(inst.definitionname = "Name der 4. Definition",1,0)) AS 'Definition 4 Count',
SUM(IF(inst.definitionname = "Name der 5. Definition",1,0)) AS 'Definition 5 Count',
SUM(IF(inst.definitionname = "Name der 6. Definition",1,0)) AS 'Definition 6 Count'
FROM view_instance inst WHERE inst.END IS NOT NULL AND
(inst.definitionname = "Name der 1. Definition"
OR inst.definitionname = "Name der 2. Definition"
OR inst.definitionname = "Name der 3. Definition"
OR inst.definitionname = "Name der 4. Definition"
OR inst.definitionname = "Name der 5. Definition"
OR inst.definitionname = "Name der 6. Definition")
GROUP BY YEAR(inst.END) DESC, MONTH(inst.END) DESC