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

=====Instance-Related Analyses=====
==== Number of started and completed instances for each definition ====
<code sql>
select inst1.definitionname as Prozess, count(distinct(inst1.id)) as "Number of started instances", count(distinct(inst2.id)) as "Number of completed instances", count(distinct(task.id)) as "Number of open tasks"
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
</code>

==== Number of started and completed instances as well as the number of open tasks of a process definition ====
<code sql>
select inst1.definitionname as Prozess, count(inst1.id) as "Number of started instances", count(inst2.id) as "Number of completed instances", (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 "Number of open tasks"
from view_instance inst1 left join view_instance inst2 on inst1.id=inst2.id and inst2.end is not null
where inst1.definitionname = "Name of process definition"
</code>

==== Number of started, completed, aborted and running instances per process definition ====
<code sql>
select definitionname as Definitionsname,
sum(alle) as "Started Instances",
sum(beendet) as "Completed instances (with end)",
sum(abgebrochen) as "Cancelled instances (archived without end)",
sum(laufend) as "Running instances"
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
</code>

==== Financial expenditure per instance ====
<code sql>
select inst.name as InstanzName,SUM(Cast(eff.value as Signed)) as FinancialEffort
from view_effort eff
inner join view_instance inst on eff.instanceID=inst.id  
where eff.effortType="FINANCE"
group by inst.id
</code>

==== Number of loops (>0) per instance ====
<code sql>
SELECT inst.DEFINITIONNAME AS Prozess, inst.name AS Instanz, SUM(IF(act.loopcount IS NOT NULL,1,0 )) AS NumberOfLoops 
FROM view_instance inst, view_activity act 
WHERE act.instanceID = inst.id
  GROUP BY inst.id, inst.definitionname, inst.name ORDER BY inst.definitionname​
</code>
==== Current activities and responsible persons of all open instances ====
<code sql>
SELECT
inst.name AS ProcessInstance,
inst.definitionName AS ProcessDefinition,
group_concat(act.name SEPARATOR ', ') AS Activity,
  group_concat(task.name SEPARATOR ', ') as Task,
  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
INNER JOIN view_identity ident2 ON task.pooledActor = ident2.id
WHERE inst.archiv = FALSE AND inst.END IS NULL
  GROUP BY inst.id
</code>

==== All open instances with a specific character string in the instance name ====
<code sql>
SELECT DISTINCT vi."name" AS "Instance name", vi."DEFINITIONNAME" AS "Process name"
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%'
</code>

==== Average lead time (in hh:mm:ss) of all process definition instances per month ====
<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 lead time" 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  
</code>

==== Average  lead time (in h) of all process definition instances per month ====
<code sql>
SELECT Monat, h
 AS "Average lead time in hours" 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  
</code>

==== Average lead time (in h) of all process definition instances per month ====
<code sql>
SELECT Monat, h
 AS "Average lead time in hours" 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 of definition" GROUP BY Monat ORDER BY YEAR(creationtime), MONTH(creationtime) ) AS dusub  
</code>

==== Instances per activity of a process definition (analog to the analysis in the PM-Clients)===
<code sql>
SELECT SUBSTRING_INDEX( va.name, '(L:', 1 ) AS "Activities", 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 "PROCESSNAME"
AND vi.archiv = 0
  GROUP BY SUBSTRING_INDEX(va.name, '(L:', 1 )
</code>

==== Number of started and completed instances per month and definition ====
<code sql>
Select Monat, Prozess, gestartete as "Number of started instances", beendete as "Number of completed instances"
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
</code>

==== Number of started and completed instances per month for a definition ====
<code sql>
Select Monat, Prozess, gestartete as "Number of started instances", beendete as "Number of completed instances"
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 of 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 of definition" group by Monat, Prozess) as spalten
order by jahr, monat2, Prozess
</code>

==== Number of started instances for a definition per month (columns) per year (rows) ====
<code sql>
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 
</code>


====Number of started instances for a definition per month (rows) per year (columns) =====

<code sql>
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)
</code>


==== Cumulative number of started instances for a definition per month (columns) per year (rows) ====
<code sql>
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
</code>

====Cumulative number of started instances for a definition per month (rows) per year (columns)=====

<code sql>
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
</code>

==== Minimum, maximum, and average instance lead time (in hh:mm:ss) per process definition ====
<code sql>
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 "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 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
</code>

==== Minimum, maximum, and average instance lead time (in h) per process definition ====
<code sql>
SELECT  definitionname AS Prozess,
CAST((avg((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime)))/3600) AS DECIMAL(10,1)) AS "Average lead time",
CAST((MIN((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime))/3600)) AS DECIMAL(10,1)) "Minimum lead time",
CAST((MAX((unix_timestamp(inst.END) - unix_timestamp(inst.creationtime))/3600)) AS DECIMAL(10,1))  AS "Maximum lead time"
FROM view_instance inst
WHERE inst.END IS NOT NULL AND (unix_timestamp(inst.END)-unix_timestamp(inst.creationtime)) > 0 GROUP BY definitionname
</code>
==== Average lead time of an instance (in hh:mm:ss) for a specified process definition per month ====
<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 lead time" 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 of process definition" GROUP BY YEAR(vi.creationtime), MONTH(vi.creationtime)) AS dusub
GROUP BY YEAR(creationtime), MONTH(creationtime)
</code>


==== Number of started instances per month for a single definition ====

<code sql>
SELECT concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat,
  SUM(IF(inst.definitionname ="Name of processdefinition",1,0)) AS "Anzahl"
  FROM view_instance inst WHERE inst.definitionname = "Name of process definition"
  GROUP BY YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC
</code>



==== Number of started instances per month for two definitions ====
<code sql>
select concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat,
  sum(IF(inst.definitionname ="Name of definition 1",1,0)) AS "Definition 1 Count",
  sum(IF(inst.definitionname = "Name of definition 2",1,0)) AS 'Definition 2 Count'
  from view_instance inst where inst.definitionname = "Name of definition 1"
  OR inst.definitionname = "Name of definition 2"
  group by YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC
  
</code>

==== Number of started instances per month for three definitions ====
<code sql>
select concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat,
  sum(IF(inst.definitionname ="Name of definition 1",1,0)) AS "Definition 1 Count",
  sum(IF(inst.definitionname = "Name of definition 2",1,0)) AS 'Definition 2 Count' ,
  sum(IF(inst.definitionname = "Name of definition 3",1,0)) AS 'Definition 3 Count'
  from view_instance inst where inst.definitionname = "Name of definition 1"
  OR inst.definitionname = "Name of definition 2"
  OR inst.definitionname = "Name of definition 3"
  group by YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC
</code>

==== Number of started instances per month for four definitions ====
<code sql>
select concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat,
  sum(IF(inst.definitionname ="Name of definition 1",1,0)) AS 'Definition 1 Count',
  sum(IF(inst.definitionname = "Name of definition 2",1,0)) AS 'Definition 2 Count',
  sum(IF(inst.definitionname = "Name of definition 3",1,0)) AS 'Definition 3 Count',
  sum(IF(inst.definitionname = "Name of definition 4",1,0)) AS 'Definition 4 Count'
  from view_instance inst where inst.definitionname = "Name of definition 1"
  OR inst.definitionname = "Name of definition 2"
  OR inst.definitionname = "Name of definition 3"
  OR inst.definitionname = "Name of definition 4"
  group by YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC
</code>

==== Number of started instances per month for five definitions ====
<code sql>
SELECT concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat,
  SUM(IF(inst.definitionname ="Name of definition 1",1,0)) AS 'Definition 1 Count',
  SUM(IF(inst.definitionname = "Name of definition 2",1,0)) AS 'Definition 2 Count',
  SUM(IF(inst.definitionname = "Name of definition 3",1,0)) AS 'Definition 3 Count',
  SUM(IF(inst.definitionname = "Name of definition 4",1,0)) AS 'Definition 4 Count',
  SUM(IF(inst.definitionname = "Name of definition 5",1,0)) AS 'Definition 5 Count'
  FROM view_instance inst WHERE inst.definitionname = "Name of definition 1"
  OR inst.definitionname = "Name of definition 2"
  OR inst.definitionname = "Name of definition 3"
  OR inst.definitionname = "Name of definition 4"
  OR inst.definitionname = "Name of definition 5"
  GROUP BY YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC


</code>

==== Number of started instances per month for six definitions ====
<code sql>


SELECT concat(SUBSTRING(monthname(inst.creationtime),1,3), " " ,SUBSTRING(CAST(YEAR(inst.creationtime) AS CHAR),3,2)) AS Monat,
  SUM(IF(inst.definitionname ="Name of definition 1",1,0)) AS 'Definition 1 Count',
  SUM(IF(inst.definitionname = "Name of definition 2",1,0)) AS 'Definition 2 Count',
  SUM(IF(inst.definitionname = "Name of definition 3",1,0)) AS 'Definition 3 Count',
  SUM(IF(inst.definitionname = "Name of definition 4",1,0)) AS 'Definition 4 Count',
  SUM(IF(inst.definitionname = "Name of definition 5",1,0)) AS 'Definition 5 Count',
  SUM(IF(inst.definitionname = "Name of definition 6",1,0)) AS 'Definition 6 Count'
  FROM view_instance inst WHERE inst.definitionname = "Name of definition 1"
  OR inst.definitionname = "Name of definition 2"
  OR inst.definitionname = "Name of definition 3"
  OR inst.definitionname = "Name of definition 4"
  OR inst.definitionname = "Name of definition 5"
  OR inst.definitionname = "Name of definition 6"
  GROUP BY YEAR(inst.creationtime) DESC, MONTH(inst.creationtime) DESC


</code>



==== Number of ended instances per month for a single definition ====
<code sql>

SELECT concat(SUBSTRING(monthname(inst.END),1,3), " " ,SUBSTRING(CAST(YEAR(inst.END) AS CHAR),3,2)) AS Monat,
  SUM(IF(inst.definitionname ="Name of definition", 1,0)) AS 'Anzahl'
  FROM view_instance inst WHERE inst.END IS NOT NULL AND
  inst.definitionname = "Name of definition"
  GROUP BY YEAR(inst.END) DESC, MONTH(inst.END) DESC

</code>


==== Number of completed instances per month for two definitions ====
<code sql>
SELECT concat(SUBSTRING(monthname(inst.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst.end) AS CHAR),3,2)) AS Monat,
  SUM(IF(inst.definitionname ="Name of definition 1", 1,0)) AS 'Definition 1 Count',
  SUM(IF(inst.definitionname = "Name of definition 2",1,0)) AS 'Definition 2 Count'
  FROM view_instance inst WHERE inst.end IS NOT NULL AND
  (inst.definitionname = "Name of definition 1"
  OR inst.definitionname = "Name of definition 2")
  GROUP BY YEAR(inst.end) DESC, MONTH(inst.end) DESC
</code>

==== Number of ended instances per month for three definitions ====
<code sql>
SELECT concat(SUBSTRING(monthname(inst.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst.end) AS CHAR),3,2)) AS Monat,
  SUM(IF(inst.definitionname ="Name of definition 1",1,0)) AS 'Definition 1 Count',
  SUM(IF(inst.definitionname = "Name of definition 2",1,0)) AS 'Definition 2 Count',
  SUM(IF(inst.definitionname = "Name of definition 3",1,0)) AS 'Definition 3 Count'
  FROM view_instance inst WHERE inst.end IS NOT NULL AND
  (inst.definitionname = "Name of definition 1"
  OR inst.definitionname = "Name of definition 2"
  OR inst.definitionname = "Name of definition 3")
  GROUP BY YEAR(inst.end) DESC, MONTH(inst.end) DESC
</code>

==== Number of ended instances per month for four definitions ====
<code sql>
SELECT concat(SUBSTRING(monthname(inst.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst.end) AS CHAR),3,2)) AS Monat,
  SUM(IF(inst.definitionname ="Name of definition 1", 1,0)) AS 'Definition 1 Count',
  SUM(IF(inst.definitionname = "Name of definition 2",1,0)) AS 'Definition 2 Count',
  SUM(IF(inst.definitionname = "Name of definition 3",1,0)) AS 'Definition 3 Count',
  SUM(IF(inst.definitionname = "Name of definition 4",1,0)) AS 'Definition 4 Count'
  FROM view_instance inst WHERE inst.end IS NOT NULL AND
  (inst.definitionname = "Name of definition 1"
  OR inst.definitionname = "Name of definition 2"
  OR inst.definitionname = "Name of definition 3"
  OR inst.definitionname = "Name of definition 4")
  GROUP BY YEAR(inst.end) DESC, MONTH(inst.end) DESC
</code>

==== Number of ended instances per month for five definitions ====
<code sql>
SELECT concat(SUBSTRING(monthname(inst.end),1,3), " " ,SUBSTRING(CAST(YEAR(inst.end) AS CHAR),3,2)) AS Monat,
  SUM(IF(inst.definitionname ="Name of definition 1", 1,0)) AS 'Definition 1 Count',
  SUM(IF(inst.definitionname = "Name of definition 2",1,0)) AS 'Definition 2 Count',
  SUM(IF(inst.definitionname = "Name of definition 3",1,0)) AS 'Definition 3 Count',
  SUM(IF(inst.definitionname = "Name of definition 4",1,0)) AS 'Definition 4 Count',
  SUM(IF(inst.definitionname = "Name of definition 5",1,0)) AS 'Definition 5 Count'
  FROM view_instance inst WHERE inst.end IS NOT NULL AND
  (inst.definitionname = "Name of definition 1"
  OR inst.definitionname = "Name of definition 2"
  OR inst.definitionname = "Name of definition 3"
  OR inst.definitionname = "Name of definition 4"
  OR inst.definitionname = "Name of definition 5")
  GROUP BY YEAR(inst.end) DESC, MONTH(inst.end) DESC
</code>


==== Number of ended instances per month for six definitions ====
<code sql>


SELECT concat(SUBSTRING(monthname(inst.END),1,3), " " ,SUBSTRING(CAST(YEAR(inst.END) AS CHAR),3,2)) AS Monat,
  SUM(IF(inst.definitionname ="Name of definition 1", 1,0)) AS 'Definition 1 Count',
  SUM(IF(inst.definitionname = "Name of definition 2",1,0)) AS 'Definition 2 Count',
  SUM(IF(inst.definitionname = "Name of definition 3",1,0)) AS 'Definition 3 Count',
  SUM(IF(inst.definitionname = "Name of definition 4",1,0)) AS 'Definition 4 Count',
  SUM(IF(inst.definitionname = "Name of definition 5",1,0)) AS 'Definition 5 Count',
  SUM(IF(inst.definitionname = "Name of definition 6",1,0)) AS 'Definition 6 Count'
  FROM view_instance inst WHERE inst.END IS NOT NULL AND
  (inst.definitionname = "Name of definition 1"
  OR inst.definitionname = "Name of definition 2"
  OR inst.definitionname = "Name of definition 3"
  OR inst.definitionname = "Name of definition 4"
  OR inst.definitionname = "Name of definition 5"
  OR inst.definitionname = "Name of definition 6")
  GROUP BY YEAR(inst.END) DESC, MONTH(inst.END) DESC


</code>