Plugin installed incorrectly. Rename plugin directory 'swiftmail.backup' to 'swiftmail'.
This translation is older than the original page and might be outdated. See what has changed.
Table of Contents
Instance-Related Analyses
Number of started instances per day
SELECT concat(to_char(inst.creationtime,'yy'), concat(to_char(inst.creationtime,'mm'), to_char(inst.creationtime,'dd'))) AS DAY, SUM(1) AS COUNT FROM view_instance inst GROUP BY concat(to_char(inst.creationtime,'yy'), concat(to_char(inst.creationtime,'mm'), to_char(inst.creationtime,'dd')))
Average number of started instances per day per month (22 working days a month)
SELECT concat(to_char(inst.creationtime,'yy'), to_char(inst.creationtime,'mm')) AS MONTH, ROUND(SUM(1)/22,1) AS "Average per day" FROM view_instance inst GROUP BY concat(to_char(inst.creationtime,'yy'), to_char(inst.creationtime,'mm'))
Number of started instances per month
SELECT concat(to_char(inst.creationtime,'yy'), to_char(inst.creationtime,'mm')) AS MONTH, SUM(1) AS COUNT FROM view_instance inst GROUP BY concat(to_char(inst.creationtime,'yy'), to_char(inst.creationtime,'mm'))
Number of started and completed instances for each definition
SELECT inst1.definitionname AS "Process", COUNT(DISTINCT(inst1.id)) AS "Number of started instances", COUNT(DISTINCT(inst2.id)) AS "Number of completed instances" 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"= NULL AND act."start" IS NOT NULL AND act."end" = NULL AND inst1."END" = NULL AND inst1.archiv = 0 GROUP BY inst1."DEFINITIONNAME"
Number of started and completed instances as well as number of open tasks of one process definition
SELECT inst1."DEFINITIONNAME" AS "Process", 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 = '1' AND act."end" IS NULL AND inst."END" IS NULL AND inst.archiv = 0 AND inst.definitionname = inst1."DEFINITIONNAME") 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" = 'S-AP-G' GROUP BY inst1.definitionname
Number of started, completed, cancelled and running instances for each process definition
SELECT definitionname, SUM(1) AS "all", SUM( CASE WHEN archiv = 1 AND END IS NULL THEN 1 ELSE 0 END) AS "cancelled", SUM( CASE WHEN END IS NOT NULL THEN 1 ELSE 0 END) AS "completed", SUM( CASE WHEN END IS NULL AND archiv = 0 THEN 1 ELSE 0 END) AS "running" FROM view_instance GROUP BY definitionname
Financial efforts for each instance
SELECT inst."name" AS "Instance name", SUM(CAST(eff.VALUE AS INT)) AS "Financial efforts" FROM view_effort eff INNER JOIN view_instance inst ON eff.instanceID=inst.id WHERE eff.effortType='FINANCE' GROUP BY inst.id, inst."name"
Number of loops (>0) for each instance
SELECT inst.DEFINITIONNAME AS Process, inst."name" AS Instance, (act.loopcount-1) AS "Number of loops" FROM view_instance inst, view_activity act WHERE act.loopcount IS NOT NULL AND act.instanceID = inst.id GROUP BY inst.id, inst.definitionname, inst."name", act.loopcount ORDER BY inst.definitionname
Activities, tasks and assigned users of running instances
SELECT inst."name" AS Instance inst.definitionName AS Process LISTAGG(act."name", ', ') WITHIN GROUP (ORDER BY act."name") AS "Activity", LISTAGG(task."name", ', ') WITHIN GROUP (ORDER BY task."name") AS "Tasks", LISTAGG( CASE WHEN ident.id IS NULL THEN ident2."name" ELSE CONCAT(CONCAT(CONCAT(CONCAT(CONCAT(ident.firstname, ' '), ident.lastname),' ('), ident2."name"),')') END , ', ') WITHIN GROUP (ORDER BY 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 = 0 AND inst.END IS NULL GROUP BY inst.id, inst."name", inst.definitionName
All running instances for which the name contains a certain string
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%'
Average lead time (in hh:mm:ss) of all instances of all process definition per month
SELECT MONTH, CASE WHEN FLOOR(sekunden)<60 THEN CONCAT(CASE WHEN FLOOR(sekunden)<10 THEN '00:0' ELSE '00:' END,FLOOR(sekunden)) WHEN FLOOR(sekunden)>3600 THEN CONCAT(CONCAT(CONCAT(CONCAT(h,CASE WHEN FLOOR (FLOOR(sekunden)- h*3600-sek)/60<10 THEN ':0' ELSE ':' END),FLOOR(FLOOR(sekunden)- h*3600-sek)/60),CASE WHEN sek<10 THEN ':0' ELSE ':' END),sek) ELSE CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(sekunden/60)<10 THEN '00:0' ELSE '00:' END ,FLOOR(sekunden/60)), CASE WHEN FLOOR(sek)<10 THEN ':0' ELSE ':' END),FLOOR(sek)) END AS "Average lead time" FROM (SELECT FLOOR(AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS'))))) AS sekunden, FLOOR(MOD(AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS')))),60)) AS sek, FLOOR(AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS'))))/3600) AS h, CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS MONTH FROM view_instance WHERE END IS NOT NULL GROUP BY CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) )
Average lead time (in hh:mm:ss) of all instances of one process definition per month
SELECT MONTH, CASE WHEN FLOOR(sekunden)<60 THEN CONCAT(CASE WHEN FLOOR(sekunden)<10 THEN '00:0' ELSE '00:' END,FLOOR(sekunden)) WHEN FLOOR(sekunden)>3600 THEN CONCAT(CONCAT(CONCAT(CONCAT(h,CASE WHEN FLOOR (FLOOR(sekunden)- h*3600-sek)/60<10 THEN ':0' ELSE ':' END),FLOOR(FLOOR(sekunden)- h*3600-sek)/60),CASE WHEN sek<10 THEN ':0' ELSE ':' END),sek) ELSE CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(sekunden/60)<10 THEN '00:0' ELSE '00:' END ,FLOOR(sekunden/60)), CASE WHEN FLOOR(sek)<10 THEN ':0' ELSE ':' END),FLOOR(sek)) END AS "Average lead time" FROM (SELECT FLOOR(AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS'))))) AS sekunden, FLOOR(MOD(AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS')))),60)) AS sek, FLOOR(AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS'))))/3600) AS h, CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS MONTH, EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS j, EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS m FROM view_instance WHERE END IS NOT NULL AND definitionName = 'NAME OF PROCESS DEFINITION' GROUP BY CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))), EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) ) ORDER BY j, m
Average lead time (in h) of all instances of all process definitions per month
SELECT MONTH, h AS "Average lead time in h" FROM (SELECT CAST((AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS'))))/3600) AS DECIMAL(10,1)) AS h, CONCAT(CONCAT( EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) ,' '),EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS MONTH FROM view_instance WHERE END IS NOT NULL GROUP BY CONCAT(CONCAT( EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) ,' '),EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) )
Average lead time (in h) of all instances of one process definition per month
SELECT MONTH AS "Month", h AS "Average lead time in h" FROM (SELECT CAST((AVG((TO_NUMBER (TO_CHAR (END, 'J')) - TO_NUMBER (TO_CHAR (creationtime, 'J'))) * 86400 + ( TO_NUMBER (TO_CHAR (END, 'SSSSS')) - TO_NUMBER (TO_CHAR (creationtime, 'SSSSS'))))/3600) AS DECIMAL(10,1)) AS h, CONCAT(CONCAT( EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) ,' '),EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS MONTH FROM view_instance WHERE END IS NOT NULL AND definitionname = 'S-AP-G' GROUP BY CONCAT(CONCAT( EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) ,' '),EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) )
Instances per activity of a process definition (analog to analysis in PM-clients)
SELECT SUBSTR(va."name", INSTR(va."name",'(L:')) AS "Activities", COUNT(va."name") AS "Count" 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 = 'NAME OF THE PROCESSDEFINITION' AND vi.archiv = 0 GROUP BY SUBSTR(va."name", INSTR(va."name",'(L:'))
Number of started and finalized instances per month and definition for x definitions
SELECT "q1Monat" AS "Month", "q1Prozess" AS "Process", "q1Gestartete" AS "Count started instances", "q1Beendete" AS "count completed instances" FROM ( SELECT COUNT(DISTINCT(inst1.id)) AS "q1Gestartete", CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'), 'Month') ,1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'))) AS "q1Monat", inst1.definitionname AS "q1Prozess", COUNT(DISTINCT(inst2.id)) AS "q1Beendete", EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')) AS "q1Jahr", EXTRACT(MONTH FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')) AS "q1Monat2" FROM view_instance inst1 LEFT OUTER JOIN view_instance inst2 ON to_char(to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'), 'Month') = to_char(to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'), 'Month') AND EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')) = EXTRACT(YEAR FROM to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')) AND inst1.definitionname=inst2.definitionname WHERE inst1.definitionname = 'S-AP-G' GROUP BY CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'), 'Month') ,1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'))), inst1.definitionname, EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')), EXTRACT(MONTH FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')) UNION SELECT COUNT(DISTINCT(inst1.id)) AS "q1Gestartete", CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst2.END,0,(INSTR(inst2.END, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'), 'Month'),1,3), ' ') ,EXTRACT(YEAR FROM to_date(SUBSTR(inst2.END,0,(INSTR(inst2.END, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'))) AS "q1Monat", inst2.definitionname AS "q1Prozess", COUNT(DISTINCT(inst2.id)) AS "q1Beendete", EXTRACT(YEAR FROM to_date(SUBSTR(inst2.creationtime,0,(INSTR(inst2.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')) AS "q1Jahr", EXTRACT(MONTH FROM to_date(SUBSTR(inst2.end,0,(INSTR(inst2.end, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')) AS "q1Monat2" FROM view_instance inst1 RIGHT OUTER JOIN view_instance inst2 ON to_char(to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'), 'Month') = to_char(to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'), 'Month') AND EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')) = EXTRACT(YEAR FROM to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')) AND inst1.definitionname=inst2.definitionname WHERE inst2.END IS NOT NULL AND inst1.definitionname = 'S-AP-G' GROUP BY CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'), 'Month'),1,3), ' ') ,EXTRACT(YEAR FROM to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'))), inst2.definitionname, EXTRACT(YEAR FROM to_date(SUBSTR(inst2.creationtime,0,(INSTR(inst2.creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')), EXTRACT(MONTH FROM to_date(SUBSTR(inst2.end,0,(INSTR(inst2.end, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')) ) ORDER BY "q1Jahr","q1Monat2","q1Prozess"
Number of started instances per month (columns) per year (rows) for a definition
SELECT Jahr AS "Year", SUM( CASE WHEN Monat LIKE '%January%' THEN 1 ELSE 0 END) AS "January", SUM( CASE WHEN Monat LIKE '%February%' THEN 1 ELSE 0 END) AS "February", SUM( CASE WHEN Monat LIKE '%March%' THEN 1 ELSE 0 END) AS "March", SUM( CASE WHEN Monat LIKE '%April%' THEN 1 ELSE 0 END) AS "April", SUM( CASE WHEN Monat LIKE '%May%' THEN 1 ELSE 0 END) AS "May", SUM( CASE WHEN Monat LIKE '%June%' THEN 1 ELSE 0 END) AS "June", SUM( CASE WHEN Monat LIKE '%July%' THEN 1 ELSE 0 END) AS "July", SUM( CASE WHEN Monat LIKE '%August%' THEN 1 ELSE 0 END) AS "August", SUM( CASE WHEN Monat LIKE '%September%' THEN 1 ELSE 0 END) AS "September", SUM( CASE WHEN Monat LIKE '%October%' THEN 1 ELSE 0 END) AS "October", SUM( CASE WHEN Monat LIKE '%November%' THEN 1 ELSE 0 END) AS "November", SUM( CASE WHEN Monat LIKE '%December%' THEN 1 ELSE 0 END) AS "December" FROM (SELECT to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') AS Monat, EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS Jahr, definitionname FROM view_instance WHERE definitionname = 'S-AP-G' ) GROUP BY Jahr
Number of started instances per month (rows) per year (columns) for a definition
SELECT Monat AS "Month", SUM( CASE WHEN Jahr = '14' THEN 1 ELSE 0 END) AS "2014", SUM( CASE WHEN Jahr = '15' THEN 1 ELSE 0 END) AS "2015" FROM (SELECT to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'), 'Month') AS Monat, EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')) AS Jahr, EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS m, definitionname FROM view_instance vi WHERE definitionname = 'PROZESSDEFINITION' ) GROUP BY Monat,m ORDER BY m
Cumulative number of started instances per month (columns) per year (rows) for a definition
SELECT Jahr AS "Year", SUM( CASE WHEN Monat LIKE '%January%' THEN 1 ELSE 0 END) AS "January", SUM( CASE WHEN Monat LIKE '%January%' OR Monat LIKE '%February%' THEN 1 ELSE 0 END) AS "February", SUM( CASE WHEN Monat LIKE '%January%' OR Monat LIKE '%February%' OR Monat LIKE '%March%' THEN 1 ELSE 0 END) AS "March", SUM( CASE WHEN Monat LIKE '%January%' OR Monat LIKE '%February%' OR Monat LIKE '%March%' OR Monat LIKE '%April%' THEN 1 ELSE 0 END) AS "April", SUM( CASE WHEN Monat LIKE '%January%' OR Monat LIKE '%February%' OR Monat LIKE '%March%' OR Monat LIKE '%April%' OR Monat LIKE '%May%' THEN 1 ELSE 0 END) AS "May", SUM( CASE WHEN Monat LIKE '%January%' OR Monat LIKE '%February%' OR Monat LIKE '%March%' OR Monat LIKE '%April%' OR Monat LIKE '%May%' OR Monat LIKE '%June%' THEN 1 ELSE 0 END) AS "June", SUM( CASE WHEN Monat LIKE '%January%' OR Monat LIKE '%February%' OR Monat LIKE '%March%' OR Monat LIKE '%April%' OR Monat LIKE '%May%' OR Monat LIKE '%June%' OR Monat LIKE '%July%' THEN 1 ELSE 0 END) AS "July", SUM( CASE WHEN Monat LIKE '%January%' OR Monat LIKE '%February%' OR Monat LIKE '%March%' OR Monat LIKE '%April%' OR Monat LIKE '%May%' OR Monat LIKE '%June%' OR Monat LIKE '%July%' OR Monat LIKE '%August%' THEN 1 ELSE 0 END) AS "August", SUM( CASE WHEN Monat LIKE '%January%' OR Monat LIKE '%February%' OR Monat LIKE '%March%' OR Monat LIKE '%April%' OR Monat LIKE '%May%' OR Monat LIKE '%June%' OR Monat LIKE '%July%' OR Monat LIKE '%August%' OR MOnat LIKE '%September%' THEN 1 ELSE 0 END) AS "September", SUM( CASE WHEN Monat LIKE '%January%' OR Monat LIKE '%February%' OR Monat LIKE '%March%' OR Monat LIKE '%April%' OR Monat LIKE '%May%' OR Monat LIKE '%June%' OR Monat LIKE '%July%' OR Monat LIKE '%August%' OR MOnat LIKE '%September%' OR Monat LIKE '%October%' THEN 1 ELSE 0 END) AS "October", SUM( CASE WHEN Monat LIKE '%January%' OR Monat LIKE '%February%' OR Monat LIKE '%March%' OR Monat LIKE '%April%' OR Monat LIKE '%May%' OR Monat LIKE '%June%' OR Monat LIKE '%July%' OR Monat LIKE '%August%' OR MOnat LIKE '%September%' OR Monat LIKE '%October%' OR Monat LIKE '%November%' THEN 1 ELSE 0 END) AS "November", SUM( CASE WHEN Monat LIKE '%January%' OR Monat LIKE '%February%' OR Monat LIKE '%March%' OR Monat LIKE '%April%' OR Monat LIKE '%May%' OR Monat LIKE '%June%' OR Monat LIKE '%July%' OR Monat LIKE '%August%' OR MOnat LIKE '%September%' OR Monat LIKE '%October%' OR Monat LIKE '%November%' OR Monat LIKE '%December%' THEN 1 ELSE 0 END) AS "December" FROM (SELECT to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS'), 'Month') AS Monat, EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YYYY HH24:MI:SS')) AS Jahr FROM view_instance WHERE definitionname = 'S-AP-G' ) GROUP BY Jahr
Cumulative number of started instances per month (rows) per year (columns) for a definition
SELECT Monat AS "Month", (SELECT SUM("a2015") FROM (SELECT aMonat, aMonatzahl, SUM(CASE WHEN aJahr = '15' THEN 1 ELSE 0 END) AS "a2015" FROM ( SELECT to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') AS aMonat, EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS aJahr, EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS aMonatzahl FROM view_instance WHERE definitionname = 'PROZESSDEFINITIONSNAME' ) GROUP BY aMonat, aMonatzahl ORDER BY aMonatzahl ) WHERE aMonatzahl <= Monatzahl) AS "Sum 2015", (SELECT SUM("b2016") FROM (SELECT bMonat, bMonatzahl, SUM(CASE WHEN bJahr = '16' THEN 1 ELSE 0 END) AS "b2016" FROM ( SELECT to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') AS bMonat, EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS bJahr, EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS bMonatzahl FROM view_instance WHERE definitionname = 'PROZESSDEFINITIONSNAME' ) GROUP BY bMonat, bMonatzahl ORDER BY bMonatzahl ) WHERE bMonatzahl <= Monatzahl) AS "Sum 2016", (SELECT SUM("c2017") FROM (SELECT cMonat, cMonatzahl, SUM(CASE WHEN cJahr = '17' THEN 1 ELSE 0 END) AS "c2017" FROM ( SELECT to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') AS cMonat, EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS cJahr, EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS cMonatzahl FROM view_instance WHERE definitionname = 'PROZESSDEFINITIONSNAME' ) GROUP BY cMonat, cMonatzahl ORDER BY cMonatzahl ) WHERE cMonatzahl <= Monatzahl) AS "Sum 2017" FROM (SELECT Monat, Monatzahl, SUM(CASE WHEN Jahr = '15' THEN 1 ELSE 0 END) AS "2015", SUM(CASE WHEN Jahr = '16' THEN 1 ELSE 0 END) AS "2016", SUM(CASE WHEN Jahr = '17' THEN 1 ELSE 0 END) AS "2017" FROM (SELECT to_char(to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month') AS Monat, EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS Jahr, EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) AS Monatzahl FROM view_instance WHERE definitionname = 'PROZESSDEFINITIONSNAME' ) GROUP BY Monat, Monatzahl ORDER BY Monatzahl )
Minimum, maximum and average lead time (in hh:mm:ss) of instances per process definition
SELECT name AS "Process", CASE WHEN INSTR(CASE WHEN FLOOR(avgSekunden)<60 THEN CONCAT(CASE WHEN FLOOR(avgSekunden)<10 THEN '00:00:0' ELSE '00:00:' END,FLOOR(avgSekunden)) WHEN FLOOR(avgSekunden)>3600 THEN CONCAT(CONCAT(CONCAT(CONCAT(avgH, CASE WHEN FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60)<10 THEN ':0' ELSE ':' END), FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60)), CASE WHEN avgSek<10 THEN ':0' ELSE ':' END), avgSek) ELSE CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(avgSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(avgSekunden/60)), CASE WHEN FLOOR(avgSek)<10 THEN ':0' ELSE ':' END), FLOOR(avgSek)) END,'-',1,1) >0 THEN '00:00:00' ELSE CASE WHEN FLOOR(avgSekunden)<60 THEN CONCAT(CASE WHEN FLOOR(avgSekunden)<10 THEN '00:00:0' ELSE '00:00:' END,FLOOR(avgSekunden)) WHEN FLOOR(avgSekunden)>3600 THEN CONCAT(CONCAT(CONCAT(CONCAT(avgH, CASE WHEN FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60)<10 THEN ':0' ELSE ':' END), FLOOR((FLOOR(avgSekunden)- avgH*3600-avgSek)/60)), CASE WHEN avgSek<10 THEN ':0' ELSE ':' END), avgSek) ELSE CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(avgSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(avgSekunden/60)), CASE WHEN FLOOR(avgSek)<10 THEN ':0' ELSE ':' END), FLOOR(avgSek)) END END AS "Average lead time", CASE WHEN INSTR( CASE WHEN FLOOR(minSekunden)<60 THEN CONCAT(CASE WHEN FLOOR(minSekunden)<10 THEN '00:00:0' ELSE '00:00:' END, FLOOR(minSekunden)) WHEN FLOOR(minSekunden)>3600 THEN CONCAT(CONCAT(CONCAT(CONCAT(minH,CASE WHEN FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60)<10 THEN ':0' ELSE ':' END),FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60)),CASE WHEN minSek<10 THEN ':0' ELSE ':' END) ,minSek) ELSE CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(minSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(minSekunden/60)),CASE WHEN FLOOR(minSek)<10 THEN ':0' ELSE ':' END),FLOOR(minSek)) END ,'-',1,1) >0 THEN '00:00:00' ELSE CASE WHEN FLOOR(minSekunden)<60 THEN CONCAT(CASE WHEN FLOOR(minSekunden)<10 THEN '00:00:0' ELSE '00:00:' END, FLOOR(minSekunden)) WHEN FLOOR(minSekunden)>3600 THEN CONCAT(CONCAT(CONCAT(CONCAT(minH,CASE WHEN FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60)<10 THEN ':0' ELSE ':' END),FLOOR((FLOOR(minSekunden)- minH*3600-minSek)/60)),CASE WHEN minSek<10 THEN ':0' ELSE ':' END) ,minSek) ELSE CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(minSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(minSekunden/60)),CASE WHEN FLOOR(minSek)<10 THEN ':0' ELSE ':' END),FLOOR(minSek)) END END AS "Minimum lead time", CASE WHEN INSTR( CASE WHEN FLOOR(maxSekunden)<60 THEN CONCAT(CASE WHEN FLOOR(maxSekunden)<10 THEN '00:00:0' ELSE '00:00:' END, FLOOR(maxSekunden)) WHEN FLOOR(maxSekunden)>3600 THEN CONCAT(CONCAT(CONCAT(CONCAT(maxH,CASE WHEN FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60)<10 THEN ':0' ELSE ':' END),FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60)),CASE WHEN maxSek<10 THEN ':0' ELSE ':' END) ,maxSek) ELSE CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(maxSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(maxSekunden/60)),CASE WHEN FLOOR(maxSek)<10 THEN ':0' ELSE ':' END),FLOOR(maxSek)) END,'-',1,1) >0 THEN '00:00:00' ELSE CASE WHEN FLOOR(maxSekunden)<60 THEN CONCAT(CASE WHEN FLOOR(maxSekunden)<10 THEN '00:00:0' ELSE '00:00:' END, FLOOR(maxSekunden)) WHEN FLOOR(maxSekunden)>3600 THEN CONCAT(CONCAT(CONCAT(CONCAT(maxH,CASE WHEN FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60)<10 THEN ':0' ELSE ':' END),FLOOR((FLOOR(maxSekunden)- maxH*3600-maxSek)/60)),CASE WHEN maxSek<10 THEN ':0' ELSE ':' END) ,maxSek) ELSE CONCAT(CONCAT(CONCAT(CASE WHEN FLOOR(maxSekunden/60)<10 THEN '00:0' ELSE '00:' END,FLOOR(maxSekunden/60)),CASE WHEN FLOOR(maxSek)<10 THEN ':0' ELSE ':' END),FLOOR(maxSek)) END END AS "Maximum lead time" FROM ( SELECT name, minSekunden, minSek, minH, maxSekunden, maxSek, maxH, avgSekunden, avgSek, avgH FROM ( SELECT FLOOR(MIN(( ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) ))) AS minSekunden, definitionname AS name FROM view_instance inst WHERE inst.END IS NOT NULL GROUP BY definitionname ), ( SELECT FLOOR(MOD(MIN(( ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) )),60)) AS minSek, definitionname AS name2 FROM view_instance inst WHERE inst.END IS NOT NULL GROUP BY definitionname ), ( SELECT FLOOR(MIN(( ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) )/3600)) AS minH, definitionname AS name3 FROM view_instance inst WHERE inst.END IS NOT NULL GROUP BY definitionname ), (SELECT FLOOR(MAX(( ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) ))) AS maxSekunden, definitionname AS name4 FROM view_instance inst WHERE inst.END IS NOT NULL GROUP BY definitionname ), ( SELECT FLOOR(MOD(MAX(( ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) )),60)) AS maxSek, definitionname AS name5 FROM view_instance inst WHERE inst.END IS NOT NULL GROUP BY definitionname ), ( SELECT FLOOR(MAX(( ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) )/3600)) AS maxH, definitionname AS name6 FROM view_instance inst WHERE inst.END IS NOT NULL GROUP BY definitionname ), ( SELECT FLOOR(avg(( ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) ))) AS avgSekunden, definitionname AS name7 FROM view_instance inst WHERE inst.END IS NOT NULL GROUP BY definitionname ), ( SELECT FLOOR(MOD(avg(( ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) )),60)) AS avgSek, definitionname AS name8 FROM view_instance inst WHERE inst.END IS NOT NULL GROUP BY definitionname ), ( SELECT FLOOR(avg(( ((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) ))/3600) AS avgH, definitionname AS name9 FROM view_instance inst WHERE inst.END IS NOT NULL GROUP BY definitionname ) WHERE name = name2 AND name = name3 AND name = name4 AND name = name5 AND name = name6 AND name = name7 AND name = name8 AND name = name9 )
Minimum, maximum and average lead time (in h) of instances per process definition
SELECT definitionname AS "Process", CAST((avg((((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) ))/3600) AS DECIMAL(10,1)) AS "Average lead time", CAST((MIN((((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24))/3600)) AS DECIMAL(10,1)) "Minimum lead time", CAST((MAX((((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) - ((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24))/3600)) AS DECIMAL(10,1)) AS "Maximum lead time" FROM view_instance inst WHERE inst.END IS NOT NULL AND (((to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24) -((to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS') - DATE '1970-01-01' ) * 60 * 60 * 24)) > 0 GROUP BY definitionname
Number of started instances per month for a definition
SELECT CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS "Month", SUM( CASE WHEN inst.definitionname ='NAME OF THE PROCESSDEFINITION' THEN 1 ELSE 0 END) AS "Count" FROM view_instance inst WHERE inst.definitionname = 'NAME OF THE PROCESSDEFINITION' GROUP BY EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')))
Number of started instances per month for 2 definitions
SELECT CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS "Month", SUM( CASE WHEN inst.definitionname ='NAME OF PROZESSDEFINITION 1' THEN 1 ELSE 0 END) AS "Count Prozessdefinition 1", SUM( CASE WHEN inst.definitionname ='NAME OF PROZESSDEFINITION 2' THEN 1 ELSE 0 END) AS "Count Prozessdefinition 2" FROM view_instance inst WHERE inst.definitionname = 'NAME OF PROZESSDEFINITION 1' OR inst.definitionname = 'NAME OF PROZESSDEFINITION 2' GROUP BY EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.creationtime,0,(INSTR(inst.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))), EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) ORDER BY EXTRACT(YEAR FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), EXTRACT(MONTH FROM to_date(SUBSTR(creationtime,0,(INSTR(creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))
Number of finalized instances per month for one definition
SELECT CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS "Month", SUM( CASE WHEN inst.definitionname ='NAME OF PROCESSDEFINITION' THEN 1 ELSE 0 END) AS "NAME OF PROCESSDEFINITION Count" FROM view_instance inst WHERE inst.END IS NOT NULL AND inst.definitionname = 'NAME OF PROCESSDEFINITION' GROUP BY EXTRACT(YEAR FROM to_date(SUBSTR(END,0,(INSTR(END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')))
Number of finalized instances per month for 2 definitions
SELECT CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))) AS "Month", SUM( CASE WHEN inst.definitionname ='NAME OF PROCESSDEFINITION 1' THEN 1 ELSE 0 END) AS "NAME OF PROCESSDEFINITION1 Count", SUM( CASE WHEN inst.definitionname ='NAME OF PROCESSDEFINITION 2' THEN 1 ELSE 0 END) AS "NAME OF PROCESSDEFINITION 2 Count" FROM view_instance inst WHERE inst.END IS NOT NULL AND (inst.definitionname = 'S-AP-G' OR inst.definitionname = 'S-AP-010-010_1') GROUP BY EXTRACT(YEAR FROM to_date(SUBSTR(END,0,(INSTR(END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))), EXTRACT(YEAR FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), EXTRACT(MONTH FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) ORDER BY EXTRACT(YEAR FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')), EXTRACT(MONTH FROM to_date(SUBSTR(inst.END,0,(INSTR(inst.END, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))
en/software/dashboard/analyses/instance_analyses_oracle.txt · Last modified: 2021/07/01 09:52 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license: 2013 T!M Solutions GmbH
