Page Comparison

SELECT
concat(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime)) AS Month, 
SUM(1) AS Count
FROM view_instance inst
GROUP BY concat(DATEPART(yy, inst.creationTime), '-', DATEPART(mm, inst.creationTime))

SELECT
concat(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime)) AS Month, 
ROUND(SUM(1)/22,1) AS "Average per day"
FROM view_instance inst
GROUP BY concat(DATEPART(yy, inst.creationTime), '-', DATEPART(mm, inst.creationTime))

SELECT 
inst1.definitionname AS Prozess, 
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.instanceEnd IS NOT NULL
INNER JOIN view_activity act ON act.instanceid=inst1.id
GROUP BY inst1.definitionname;​

SELECT 
definitionName AS "Definition name", 
SUM(1) AS started, 
SUM(CASE WHEN isArchiv = 'false' AND instanceEnd is NULL THEN 1 ElSE 0 end) AS running, 
SUM(CASE WHEN isArchiv = 'true' And instanceEnd is NULL THEN 1 ELSE 0 END) AS aborted, 
SUM(CASE WHEN instanceEnd is NOT NULL THEN 1 ELSE 0 end) AS completed
FROM view_instance GROUP BY definitionName​

SELECT
inst.instanceName AS 'Instance',
inst.definitionName AS 'Process',
STRING_AGG(act.activityName, ', ') WITHIN GROUP (ORDER BY act.activityName) AS "Activity",
STRING_AGG(task.taskName, ', ') AS "Tasks",
CASE WHEN ident.ID IS NULL THEN ident2.IDENTITYNAME ELSE CONCAT(ident.FIRSTNAME, ' ', ident.LASTNAME,' (', ident2.IDENTITYNAME,')') END AS "Username"
FROM view_instance inst
INNER JOIN view_activity act
ON act.instanceid = inst.id
AND act.activityStart IS NOT NULL
AND act.activityEnd IS NULL AND act.activityType = 'K'
INNER JOIN view_task task
ON task.activityId = act.id
LEFT JOIN view_identity ident
ON task.actorId = ident.id
LEFT JOIN view_identity ident2
ON task.POOLEDACTORID = ident2.ID
WHERE inst.isArchiv = 'false'
AND inst.instanceEnd IS NULL
GROUP BY inst.id, inst.instanceName, inst.definitionName, act.activityName, task.taskName, ident.identityName, ident.id, ident.firstname, ident.lastname, ident2.identityname
ORDER BY inst.definitionName, act.activityName, inst.instanceName, task.taskName;

SELECT
CONCAT(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime)) AS Month,
AVG(DATEDIFF(hh, inst.instanceStart, inst.instanceEnd)) AS 'Cycle Time'
FROM view_instance inst
WHERE inst.instanceEnd IS NOT NULL  
GROUP BY CONCAT(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime))

SELECT
Year as "Year",
SUM(CASE WHEN Month LIKE '1' THEN 1 ELSE 0 END) AS "January",
SUM(CASE WHEN Month LIKE '2'THEN 1 ELSE 0 END) AS "February",
SUM(CASE WHEN Month LIKE '3' THEN 1 ELSE 0 END) AS "March",
SUM(CASE WHEN Month LIKE '4' THEN 1 ELSE 0 END) AS "April",
SUM(CASE WHEN Month LIKE '5' THEN 1 ELSE 0 END) AS "May",
SUM(CASE WHEN Month LIKE '6' THEN 1 ELSE 0 END) AS "June",
SUM(CASE WHEN Month LIKE '7' THEN 1 ELSE 0 END) AS "July",
SUM(CASE WHEN Month LIKE '8' THEN 1 ELSE 0 END) AS "August",
SUM(CASE WHEN Month LIKE '9' THEN 1 ELSE 0 END) AS "September",
SUM(CASE WHEN Month LIKE '10' THEN 1 ELSE 0 END) AS "October",
SUM(CASE WHEN Month LIKE '11' THEN 1 ELSE 0 END) AS "November",
SUM(CASE WHEN Month LIKE '12' THEN 1 ELSE 0 END) AS "December"
FROM
(SELECT
DATEPART(mm, creationTime) AS Month,
DATEPART(yy, creationTime) AS Year
FROM view_instance
WHERE definitionName = '<name_of_process_definition>'
) AS subqu
GROUP BY Year

SELECT
Year as "Year",
SUM(CASE WHEN Month LIKE '1' THEN 1 ELSE 0 END) AS "January",
SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' THEN 1 ELSE 0 END) AS "February",
SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' THEN 1 ELSE 0 END) AS "March",
SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4' THEN 1 ELSE 0 END) AS "April",
SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4' OR Month LIKE '5' THEN 1 ELSE 0 END) AS "May",
SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' THEN 1 ELSE 0 END) AS "June",
SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' OR Month LIKE '7' THEN 1 ELSE 0 END) AS "July",
SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' OR Month LIKE '7' OR Month LIKE '8' THEN 1 ELSE 0 END) AS "August",
SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' OR Month LIKE '7' OR Month LIKE '8' OR Month LIKE '9' THEN 1 ELSE 0 END) AS "September",
SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' OR Month LIKE '7' OR Month LIKE '8' OR Month LIKE '9' OR Month LIKE '10' THEN 1 ELSE 0 END) AS "October",
SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' OR Month LIKE '7' OR Month LIKE '8' OR Month LIKE '9' OR Month LIKE '10' OR Month LIKE '11' THEN 1 ELSE 0 END) AS "November",
SUM(CASE WHEN Month LIKE '1' OR Month LIKE '2' OR Month LIKE '3' OR Month LIKE '4'OR Month LIKE '5' OR Month LIKE '6' OR Month LIKE '7' OR Month LIKE '8' OR Month LIKE '9' OR Month LIKE '10' OR Month LIKE '11' OR Month LIKE '12' THEN 1 ELSE 0 END) AS "December"
FROM
(SELECT
DATEPART(mm, creationTime) AS Month,
DATEPART(yy, creationTime) AS Year
FROM view_instance
WHERE definitionName = '<name_of_process_definition>'
) AS subqu
GROUP BY Year​

SELECT
concat(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime)) AS Month,
AVG(DATEDIFF(hh, inst.instanceStart, inst.instanceEnd)) AS 'Average cycle time',
MIN(DATEDIFF(hh, inst.instanceStart, inst.instanceEnd)) AS 'Min cycle time',
MAX(DATEDIFF(hh, inst.instanceStart, inst.instanceEnd)) AS 'Max cycle time'
FROM view_instance inst
WHERE inst.instanceEnd IS NOT NULL  
GROUP BY concat(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime))

SELECT
defName AS Process,
SUM(lCount) AS NumberOfLoops
FROM
(SELECT
inst.definitionName AS defName,
inst.instanceName AS instName,
act.loopCount AS lCount,
inst.id AS piId
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.instanceName, act.loopCount
) AS subqu
GROUP BY defName;