Query to show total number of conversations, incoming and outgoing conversations (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306)
Total Number of Conversations:
SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", aau.FIRST_NAME AS "Requester", ad.ORG_NAME AS "Account", std.STATUSNAME AS "Status", ti.FIRST_NAME AS "Technician", ((select count(c.conversationid) from conversation c where c.workorderid=wo.workorderid) + (select count(n.notificationid) from notify_workorder n LEFT JOIN notification no on n.notificationid=no.notificationid where no.senderid!='1' AND n.workorderid=wo.workorderid)) "Total number of conversations" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
Number of Incoming Conversations:
select wo.workorderid "Request ID", COUNT(conv.conversationid) "Number of Incoming Conversations" FROM workorder wo LEFT JOIN conversation conv ON wo.workorderid=conv.workorderid LEFT JOIN conversationdescription convd ON conv.conversationid=convd.conversationid LEFT JOIN sduser sdu ON conv.requesterid=sdu.userid LEFT JOIN aaauser aaau ON sdu.userid=aaau.user_id WHERE conv.workorderid=wo.workorderid group by wo.workorderid
order by 1
Number of Outgoing Conversations: (Excluding system notifications):
select wo.workorderid "Request ID", COUNT(n.Notificationid) "Number of Outgoing Conversations" from workorder wo left join notify_workorder nw on wo.workorderid=nw.workorderid LEFT JOIN notification n on nw.notificationid=n.notificationid left join aaauser nau on n.senderid=nau.user_id where nau.first_name!='system' GROUP BY wo.workorderid
New to ADSelfService Plus?
Related Articles
Incoming and Outgoing conversations with Attachment name and location (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: Incoming: (Referred as 'Conversations' in the database) select conv.conversationid "Conversation ID", conv.workorderid "Request ID", wo.title "Request Subject", aaau.first_name "Sender", ...Query to show total number of calls received and resolved per month (MSSQL & PGSQL )
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT count(wo.workorderid) "Total Created", count(case when wo.completedtime >= <from_thismonth> AND wo.completedtime <= <to_thismonth> THEN 1 ELSE NULL END) "Closed" FROM WorkOrder wo ...Query to show tickets that shows total time spent of the ticket more than x hours (MSSQL)
Tested in MSSQL build (14306) Query to show tickets that shows total time spent of the ticket more than 8 hours: select ad.org_name "Account", sdo.name "Site", wo.workorderid "Request ID", au.first_name "Assigned Technician", ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to show Total Onhold time ( PGSQL )
Tested in build PGSQL (14300) PGSQL: SELECT LONGTODATE(wo.CREATEDTIME) AS "Created Time", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", ...