Process queries

This table shows the list of queries related to Process:

Use case Query
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'
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;
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;
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;