=====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'))