Page Comparison

SELECT
CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) AS Month,
  SUM(1) AS COUNT
FROM view_instance inst
  GROUP BY CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m'))
  ORDER BY CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) DESC;

SELECT
CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) AS Month,
ROUND(SUM(1)/22,1) AS "Average per day"
FROM view_instance inst
GROUP BY CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m'))
ORDER BY CONCAT(DATE_FORMAT(inst.CREATIONTIME, '%Y'), '-', DATE_FORMAT(inst.CREATIONTIME, '%m')) DESC;

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.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= '0' And instanceEnd is NULL THEN 1 ELSE 0 end) AS running,
SUM(CASE WHEN isArchiv= '1' 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",
act.ACTIVITYNAME AS "Activity",
task.TASKNAME AS "Tasks",
CASE WHEN ident.ID IS NULL THEN ident2.IDENTITYNAME ELSE CONCAT(CONCAT(CONCAT(CONCAT(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
INNER JOIN view_identity ident2
ON task.POOLEDACTORID = ident2.ID
WHERE inst.ISARCHIV = '0'
AND inst.INSTANCEEND IS NULL
GROUP BY inst.ID, inst.INSTANCENAME, inst.DEFINITIONNAME
ORDER BY inst.DEFINITIONNAME, inst.INSTANCENAME;​

SELECT
  CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) AS Month,
  IF((UNIX_TIMESTAMP(instanceEnd ) - UNIX_TIMESTAMP(instanceStart))>0, ROUND((AVG(UNIX_TIMESTAMP(instanceEnd) - UNIX_TIMESTAMP(INSTANCESTART)))/3600), '0')  AS "CYCLE TIME"
FROM view_instance
WHERE instanceEnd IS NOT NULL
  GROUP BY CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m'))
  ORDER BY CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) DESC;

SELECT
Year as "Year",
SUM(CASE WHEN Month LIKE '01' THEN 1 ELSE 0 END) AS "January",
SUM(CASE WHEN Month LIKE '02'THEN 1 ELSE 0 END) AS "February",
SUM(CASE WHEN Month LIKE '03' THEN 1 ELSE 0 END) AS "March",
SUM(CASE WHEN Month LIKE '04' THEN 1 ELSE 0 END) AS "April",
SUM(CASE WHEN Month LIKE '05' THEN 1 ELSE 0 END) AS "May",
SUM(CASE WHEN Month LIKE '06' THEN 1 ELSE 0 END) AS "June",
SUM(CASE WHEN Month LIKE '07' THEN 1 ELSE 0 END) AS "July",
SUM(CASE WHEN Month LIKE '08' THEN 1 ELSE 0 END) AS "August",
SUM(CASE WHEN Month LIKE '09' 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
DATE_FORMAT(creationTime, '%m') AS Month,
DATE_FORMAT(creationTime, '%Y') AS Year
FROM view_instance
WHERE definitionName = '<name_of_process_definition>'
) AS subqu
GROUP BY Year
ORDER BY Year;

SELECT
  CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) AS Month,
  IF((UNIX_TIMESTAMP(instanceEnd ) - UNIX_TIMESTAMP(instanceStart))>0, ROUND((AVG(UNIX_TIMESTAMP(instanceEnd) - UNIX_TIMESTAMP(INSTANCESTART)))/3600), '0')  AS "Average cycle time",
  IF((UNIX_TIMESTAMP(instanceEnd ) - UNIX_TIMESTAMP(instanceStart))>0, ROUND((MIN(UNIX_TIMESTAMP(instanceEnd) - UNIX_TIMESTAMP(INSTANCESTART)))/3600), '0')  AS "Min cycle time",
  IF((UNIX_TIMESTAMP(instanceEnd ) - UNIX_TIMESTAMP(instanceStart))>0, ROUND((MAX(UNIX_TIMESTAMP(instanceEnd) - UNIX_TIMESTAMP(INSTANCESTART)))/3600), '0')  AS "Max cycle time"
FROM view_instance
WHERE instanceEnd IS NOT NULL
  GROUP BY CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m'))
  ORDER BY CONCAT(DATE_FORMAT(creationTime, '%Y'), '-', DATE_FORMAT(creationTime, '%m')) DESC;

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",
  act.activityName AS "Activity Name"
FROM view_instance inst
LEFT JOIN view_activity act
  ON act.instanceId=inst.id
WHERE act.loopCount IS NOT NULL
GROUP BY inst.id, inst.definitionName, inst.instanceName, act.loopCount, act.activityName) AS subqu
GROUP BY defName;