Process queries

This topic provides use cases for Process queries.

Use case 1

Fetch tasks created within a time range with the specific user in the distribution list.

SELECT
 ION_PulseTaskEvent.addedUsers,
 ION_PulseTaskEvent.assignee,
 ION_PulseTaskEvent.category,
 ION_PulseTaskEvent.distributionUsers,
 ION_PulseTaskEvent.dueDate,
 ION_PulseTaskEvent.escalationLevel,
 ION_PulseTaskEvent.escalationType,
 ION_PulseTaskEvent.eventTimeStamp,
 ION_PulseTaskEvent.eventType,
 ION_PulseTaskEvent.finishParameterValue,
 ION_PulseTaskEvent.id,
 ION_PulseTaskEvent.priority,
 ION_PulseTaskEvent.removedUsers,
 ION_PulseTaskEvent.source,
 ION_PulseTaskEvent.sourceId,
 ION_PulseTaskEvent.sourceMajorVersion,
 ION_PulseTaskEvent.sourceMinorVersion,
 ION_PulseTaskEvent.sourceName,
 ION_PulseTaskEvent.sourceSubId
FROM
 ION_PulseTaskEvent
WHERE distributionUsers LIKE '%4122cf63-d5ca-4f09-b3e5-bc12a19f12e8%' AND eventTimeStamp BETWEEN '2024-07-10' AND '2024-07-31'

Use case 2

Fetch only delayed tasks and count them by reason.

SELECT 
 ION_PulseTaskEvent.escalationType, 
 COUNT(*) AS count
FROM 
 ION_PulseTaskEvent
WHERE
 escalationType IS NOT NULL
GROUP BY 
 escalationType;

Use case 3

Which employees are closing the most deals?

WITH FinishedEvents AS (
 SELECT
 ION_PulseTaskEvent.id,
 ION_PulseTaskEvent.eventTimeStamp,
 ION_PulseTaskEvent.eventType
 FROM
 ION_PulseTaskEvent
 WHERE
 eventType = 'Finished'
),
AssignedEvents AS (
 SELECT
 ION_PulseTaskEvent.id,
 ION_PulseTaskEvent.eventTimeStamp,
 ION_PulseTaskEvent.assignee
 FROM
 ION_PulseTaskEvent
 WHERE
 eventType = 'Assigned'
),
LastAssignedBeforeFinished AS (
 SELECT
 f.id,
 a.assignee
 FROM
 FinishedEvents AS f
 JOIN
 AssignedEvents AS a
 ON
 f.id = a.id
 WHERE
 a.eventTimeStamp = (
 SELECT MAX(a2.eventTimeStamp)
 FROM AssignedEvents AS a2
 WHERE a2.id = f.id 
 AND a2.eventTimeStamp < f.eventTimeStamp
 )
)
SELECT
 assignee,
 COUNT(*) AS closed_deals
FROM
 LastAssignedBeforeFinished
GROUP BY
 assignee;

Use case 4

How much time (in average) it takes users to complete their tasks in the various workflows?

WITH FinishedEvents AS (
 SELECT
 ION_PulseTaskEvent.id,
 ION_PulseTaskEvent.eventTimeStamp AS finishedTime
 FROM
 ION_PulseTaskEvent
 WHERE
 eventType = 'Finished'
),
AssignedEvents AS (
 SELECT
 ION_PulseTaskEvent.id,
 ION_PulseTaskEvent.eventTimeStamp AS assignedTime,
 assignee
 FROM
 ION_PulseTaskEvent
 WHERE
 eventType = 'Assigned'
),
LastAssignedBeforeFinished AS (
 SELECT
 f.id,
 a.assignee,
 f.finishedTime,
 a.assignedTime
 FROM
 FinishedEvents AS f
 JOIN
 AssignedEvents AS a 
 ON 
 f.id = a.id
 WHERE
 a.assignedTime = (
 SELECT 
 MAX(a2.assignedTime)
 FROM 
 AssignedEvents AS a2
 WHERE 
 a2.id = f.id
 AND a2.assignedTime < f.finishedTime
 )
)
SELECT
 assignee,
 AVG(DATEDIFF(SECOND, assignedTime, finishedTime)) AS avg_time_to_close
FROM
 LastAssignedBeforeFinished
GROUP BY
 assignee;