Copy of DE MSSQL Analyses related to tasks and activities

SELECT
  ident.lastname AS "Lastname",
  ident.firstname AS "Firstsname",
  ident.identityName AS "Username",
  task.taskName AS "Task",
  inst.instanceName AS "Instance name",
  inst.definitionName AS "Processname"
FROM view_activity act, view_task task, view_identity ident, view_instance inst
WHERE act.id = task.activityId
  AND task.actorId = ident.id
  AND act.activityEnd IS NULL
  AND task.taskEnd IS NULL
  AND inst.id = act.instanceId
  AND inst.isArchiv = 'false'
  AND inst.instanceEnd IS NULL
  AND ident.FIRSTNAME = '<specific_user_firstname>'
  AND ident.lastname = '<specific_user_lastname>'

SELECT
  inst.definitionName AS "Definition name",
  SUM(1) AS "Number of created tasks",
  Sum(CASE WHEN task.taskEnd IS NOT NULL THEN 1 ELSE 0 END) AS "Number of completed tasks",
  SUM(CASE WHEN task.taskEnd is NULL AND task.isArchiv = 'false' THEN 1 ELSE 0 END) AS "Number of open tasks"
FROM view_task task
  INNER JOIN view_instance inst ON task.instanceID=inst.id
  GROUP BY inst.definitionName​

SELECT
  concat(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime)) AS Month,
  act.activityName AS "Activity",
  AVG(DATEDIFF(hh, act.activityStart, act.activityEnd)) AS 'Average cycle time',
  MIN(DATEDIFF(hh, act.activityStart, act.activityEnd)) AS 'Min cycle time',
  MAX(DATEDIFF(hh, act.activityStart, act.activityEnd)) AS 'Max cycle time'
FROM view_activity act
LEFT JOIN view_instance inst ON act.instanceId=inst.id
WHERE act.activityEnd IS NOT NULL  
  AND act.activityStart IS NOT NULL
  AND act.activityType IN ('K','S')
  AND inst.definitionName = '<name_of_process_definition>'
GROUP BY concat(DATEPART(yy, inst.creationTime),'-', DATEPART(mm, inst.creationTime)), act.activityName;