MSSQL Analyses related to tasks and activities

1 Task- and Activity-related
- 1.1 Task-Related
  - 1.1.1 All open tasks for a specific user
  - 1.1.2 Number of created, completed, and open tasks for each process definition
- 1.2 Activity-Related
  - 1.2.1 Average, minimum and maximum cycle time of all activities of a specific process definition (in h) per month

This article offers examples for task and activity related analyses with the dashboard.

All open tasks for a specific user

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

Number of created, completed, and open tasks for each process definition

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

Average, minimum and maximum cycle time of all activities of a specific process definition (in h) per month

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;

Task- and Activity-related

Task-Related

All open tasks for a specific user

Number of created, completed, and open tasks for each process definition

Activity-Related

Average, minimum and maximum cycle time of all activities of a specific process definition (in h) per month