=====Instanzbezogene Auswertungen=====
==== Anzahl an gestarteten Instanzen je Tag ====
<code sql>
SELECT 
  concat(to_char(inst.creationtime,'yy'), concat(to_char(inst.creationtime,'mm'), to_char(inst.creationtime,'dd'))) AS Tag, SUM(1) as Anzahl
  FROM view_instance inst
  group by concat(to_char(inst.creationtime,'yy'), concat(to_char(inst.creationtime,'mm'), to_char(inst.creationtime,'dd')))
</code>

==== Durchschnittliche Anzahl an gestarteten Instanzen je Tag pro Monat (bei 22 Arbeitstagen im Monat) ====
<code sql>
SELECT 
  concat(to_char(inst.creationtime,'yy'), to_char(inst.creationtime,'mm')) AS Monat, ROUND(SUM(1)/22,1) as "Durchschnitt pro Tag"
  FROM view_instance inst
group by concat(to_char(inst.creationtime,'yy'), to_char(inst.creationtime,'mm'))
</code>

==== Anzahl an gestarteten Instanzen je Monat ====
<code sql>
SELECT 
  concat(to_char(inst.creationtime,'yy'), to_char(inst.creationtime,'mm')) AS Monat, SUM(1) as Anzahl
  FROM view_instance inst
  group by concat(to_char(inst.creationtime,'yy'), to_char(inst.creationtime,'mm'))
</code>

==== Anzahl an gestarteten und beendeten Instanzen je Definition ====
<code sql>
SELECT 
inst1.definitionname AS "Prozess", COUNT(DISTINCT(inst1.id)) AS "Anzahl gestarteter Instanzen", COUNT(DISTINCT(inst2.id)) AS "Anzahl beendeter Instanzen"
FROM view_instance inst1 
LEFT JOIN view_instance inst2 
ON inst1.id=inst2.id
AND inst2.END IS NOT NULL
GROUP BY inst1.definitionname 
</code>

==== Anzahl an gestarteten und beendeten Instanzen sowie die Anzahl an offenen Aufgaben einer Prozessdefinition ====
<code sql>
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 = '1' 
AND act.end IS NULL 
AND inst."END" IS NULL 
AND inst.archiv = 0
AND inst.definitionname = inst1."DEFINITIONNAME") 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" like 'GL-01%'
GROUP BY inst1.definitionname
</code>

==== Anzahl an gestarteten, beendeten, abgebrochenen und laufenden Instanzen je Prozessdefinition ====
<code sql>
SELECT
definitionname,
SUM(1) as "alle", 
SUM(
CASE
WHEN archiv = 1 and END IS NULL
THEN 
1
ELSE
0
END) as "abgebrochen",
SUM(
CASE
WHEN END IS NOT NULL
THEN 
1
ELSE
0
END) as "beendet",
SUM(
CASE
WHEN END IS NULL AND archiv = 0
THEN 
1
ELSE 
0
END) as "laufend"
from view_instance group by definitionname
</code>

==== Finanzielle Aufwände je Instanz ====
<code sql>
SELECT inst."name" AS Instanzname, SUM(CAST(eff.VALUE AS INT)) 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, inst."name"​
</code>

==== Anzahl der Schleifen (>0) je Instanz ====
<code sql>
SELECT 
  inst.DEFINITIONNAME AS Prozess, inst."name" AS Instanz,
 SUM(  
  CASE 
  WHEN act.loopcount IS NOT NULL
  THEN 1
  ELSE 0
  END
  ) as Schleifendurchläufe
FROM view_instance inst, view_activity act 
WHERE act.loopcount IS NOT NULL 
AND act.instanceID = inst.id
GROUP BY inst."name", inst.DEFINITIONNAME​
</code>

==== Aktuelle Aktivität(en) und Bearbeiter aller offenen Instanzen ====
<code sql>
SELECT
inst."name" AS Prozessinstanz,
inst.definitionName AS Prozessdefinition,
  LISTAGG(act."name", ', ') WITHIN GROUP (ORDER BY act."name") as "Aktivität",
  LISTAGG(task."name", ', ') WITHIN GROUP (ORDER BY task."name") as "Aufgaben",
  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
LEFT 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​
</code>

==== Alle offenen Instanzen mit einer bestimmten Zeichenfolge im Instanznamen ====
<code sql>
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%'​
</code>


==== Durchschnittliche Durchlaufszeit (in hh:mm:ss) von allen Instanzen aller Prozessdefinitionen je Monat ====
<code sql>
SELECT Monat,
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 "Durchschn. DLZ"
  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 Monat
    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')))
  )
</code>

==== Durchschnittliche Durchlaufszeit (in hh:mm:ss) der Instanzen einer bestimmten Prozessdefinition je Monat  ====

<code sql>
SELECT Monat,
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 "Durchschn. DLZ"
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 Monat,
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 DER PROZESSDEFINITION'
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
  </code>

==== Durchschnittliche Durchlaufszeit (in h) von allen Instanzen aller Prozessdefinitionen je Monat ====
<code sql>
SELECT Monat, h
AS "Durchschn. DLZ 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 Monat
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')))
)​
</code>

==== Durchschnittliche Durchlaufszeit (in h) von allen Instanzen einer Prozessdefinitionen je Monat ====
<code sql>
SELECT Monat, h
AS "Durchschn. DLZ 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 Monat
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')))
)​​​
</code>

==== Instanzen je Aktivität einer Prozessdefinition (analog zur Auswertung in den PM-Clients)===
<code sql>
SELECT 
  CASE WHEN INSTR(va.name,' (L:') > 0 THEN SUBSTR(va.name, 0, INSTR(va.name,' (L:')-1) ELSE va.name END 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 = 'PROZESSDEFINITIONSNAME' 
AND vi.archiv = 0 
GROUP BY CASE WHEN INSTR(va.name,' (L:') > 0 THEN SUBSTR(va.name, 0, INSTR(va.name,' (L:')-1) ELSE va.name END​
</code>

==== Anzahl der gestarteten und beendeten Instanzen je Monat und Definition ====
<code sql>
SELECT "q1Monat" as "Monat", "q1Prozess" as "Prozess", "q1Gestartete" as "Anzahl gestartete Instanzen", "q1Beendete" as "Anzahl beendete Instanzen"
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-YY HH24:MI:SS'), 'Month')  ,1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY 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-YY HH24:MI:SS')) AS "q1Jahr", 
    EXTRACT(MONTH FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY 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-YY HH24:MI:SS'), 'Month') = to_char(to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month')
    AND 
    EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) = EXTRACT(YEAR FROM to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))
    AND inst1.definitionname=inst2.definitionname
    GROUP BY 
    CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month')  ,1,3),' '),EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))), 
    inst1.definitionname, 
    EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')),
    EXTRACT(MONTH FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY 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-YY HH24:MI:SS'), 'Month'),1,3), ' ') ,EXTRACT(YEAR FROM to_date(SUBSTR(inst2.END,0,(INSTR(inst2.END, '.', -1)-1)), 'DD-MM-YY 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-YY HH24:MI:SS')) AS "q1Jahr",
    EXTRACT(MONTH FROM to_date(SUBSTR(inst2.end,0,(INSTR(inst2.end, '.', -1)-1)), 'DD-MM-YY 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-YY HH24:MI:SS'), 'Month') = to_char(to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month')
    AND 
    EXTRACT(YEAR FROM to_date(SUBSTR(inst1.creationtime,0,(INSTR(inst1.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) = EXTRACT(YEAR FROM to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')) 
    AND inst1.definitionname=inst2.definitionname
    WHERE inst2.END IS NOT NULL 
    GROUP BY 
     CONCAT(CONCAT(SUBSTR(to_char(to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'), 'Month'),1,3), ' ') ,EXTRACT(YEAR FROM to_date(SUBSTR(inst2."END",0,(INSTR(inst2."END", '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))), 
     inst2.definitionname,
     EXTRACT(YEAR FROM to_date(SUBSTR(inst2.creationtime,0,(INSTR(inst2.creationtime, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS')),
     EXTRACT(MONTH FROM to_date(SUBSTR(inst2.end,0,(INSTR(inst2.end, '.', -1)-1)), 'DD-MM-YY HH24:MI:SS'))
   )
ORDER BY "q1Jahr","q1Monat2","q1Prozess"
</code>

==== Anzahl der gestarteten Instanzen je Monat (Spalten) je Jahr (Zeilen) für eine Definition ====

<code sql>
SELECT
  Jahr as "Jahr",  
  SUM(  
    CASE 
    WHEN Monat LIKE '%January%'
    THEN 1
    ELSE 0
    END) AS "Januar",
   SUM(  
    CASE 
    WHEN Monat LIKE '%February%'
    THEN 1
    ELSE 0
    END) AS "Februar",
  SUM(  
    CASE 
    WHEN Monat LIKE '%March%'
    THEN 1
    ELSE 0
    END) AS "März",
  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 "Mai",
  SUM(  
    CASE 
    WHEN Monat LIKE '%June%'
    THEN 1
    ELSE 0
    END) AS "Juni",
  SUM(  
    CASE 
    WHEN Monat LIKE '%July%'
    THEN 1
    ELSE 0
    END) AS "Juli",
   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 "Oktober",
   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 "Dezember"
 
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​
</code>


====Anzahl der gestarteten Instanzen je Monat (Zeilen) je Jahr (Spalten) für eine Definition =====

<code sql>
SELECT Monat,
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​
</code>

==== Kumulierte Anzahl der gestarteten Instanzen je Monat (Spalten) je Jahr (Zeilen) für eine Definition ====
<code sql>
SELECT
  Jahr,
  SUM(  
    CASE 
    WHEN Monat like '%January%'
    THEN 1
    ELSE 0
    END) as "Januar",
  SUM(  
    CASE 
    WHEN Monat like '%January%' OR Monat like '%February%'
    THEN 1
    ELSE 0
    END) as "Februar",
  SUM(  
    CASE 
    WHEN Monat like '%January%' OR Monat like '%February%' OR Monat like '%March%'
    THEN 1
    ELSE 0
    END) as "März",
  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 "Mai",
  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 "Juni",
  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 "Juli",
  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 "Oktober",
 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 "Dezember"
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
    FROM view_instance
    WHERE definitionname = 'PROZESSDEFINITIONSNAME'
  )
GROUP BY Jahr​
</code>

====Kumulierte Anzahl der gestarteten Instanzen je Monat (Zeilen) je Jahr (Spalten) für eine Definition =====

<code sql>
SELECT Monat,
  (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 "Summe 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 "Summe 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 "Summe 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
  )​
</code>

==== Minimale, maximale und durchschnittliche Laufzeit (in hh:mm:ss) der Instanzen je Prozessdefinition ====
<code sql>
SELECT 
name as "Prozess",

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 "Durschn. DLZ",

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 "Min. DLZ",
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 "Max. DLZ"

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

==== Minimale, maximale und durchschnittliche Laufzeit (in h) der Instanzen je Prozessdefinition ====
<code sql>
SELECT definitionname AS Prozess,
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 "Durschnittliche Durchlaufszeit",
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)) "Minimale Durchlaufszeit",
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 "Maximale Durchlaufszeit"
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
</code>


==== Anzahl gestarteter Instanzen je Monat für eine Definition ====

<code sql>
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 Monat,
  SUM(
  CASE
  WHEN inst.definitionname ='PROZESSDEFINITIONSNAME'
  THEN 1
  ELSE 0
  END) AS "Anzahl"
  FROM view_instance inst WHERE inst.definitionname = 'PROZESSDEFINITIONSNAME'
  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')))
</code>



==== Anzahl gestarteter Instanzen je Monat für 2 Definitionen ====
<code sql>
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 Monat,
SUM(
CASE
WHEN inst.definitionname ='NAME DER PROZESSDEFINITION 1'
THEN 1
ELSE 0
END) AS "Count Prozessdefinition 1",
SUM(
CASE
WHEN inst.definitionname ='NAME DER PROZESSDEFINITION 2'
THEN 1
ELSE 0
END) AS "Count Prozessdefinition 2"
FROM view_instance inst 
WHERE inst.definitionname = 'NAME DER PROZESSDEFINITION 1'
OR inst.definitionname = 'NAME DER 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'))
</code>


==== Anzahl beendeter Instanzen je Monat für eine Definition ====
<code sql>

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 Monat,
  SUM(
  CASE
  WHEN inst.definitionname ='PROZESSDEFINITIONSNAME'
  THEN 1
  ELSE 0
  END) AS "PROZESSDEFINITIONSNAME Count"
  FROM view_instance inst WHERE inst.END IS NOT NULL AND
  inst.definitionname = 'PROZESSDEFINITIONSNAME'
  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')))

</code>


==== Anzahl beendeter Instanzen je Monat für 2 Definitionen ====
<code sql>
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 Monat,
SUM(
CASE
WHEN inst.definitionname ='PROZESSDEFINITIONSNAME 1'
THEN 1
ELSE 0
END) AS "PROZESSDEFINITIONSNAME 1 Count",
SUM(
CASE
WHEN inst.definitionname ='PROZESSDEFINITIONSNAME 2'
THEN 1
ELSE 0
END) AS "PROZESSDEFINITIONSNAME 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'))
</code>