Dies ist eine alte Version des Dokuments!
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
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"
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
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
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
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 INNER JOIN view_identity ident2 ON task.pooledActor = ident2.id WHERE inst.archiv = FALSE AND inst.END IS NULL GROUP BY inst.id
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%'
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
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
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
SELECT SUBSTR(va."name", INSTR(va."name",'(L:')) AS "Aktivitäten", COUNT(va."name") AS Anzahl FROM view_activity va JOIN view_instance vi ON vi.id = va.instanceId WHERE va."end" IS NULL AND va."start" IS NOT NULL AND (va."type" = 'K' OR va."type" = 'C') AND vi.definitionName = 'S-AP-G' AND vi.archiv = 0 GROUP BY SUBSTR(va."name", INSTR(va."name",'(L:'))
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
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
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
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)
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
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
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
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
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)
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
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
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
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
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
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
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
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
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
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
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
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