Query to show total number of conversations, incoming and outgoing conversations

Query to show total number of conversations, incoming and outgoing conversations

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
          • Related Articles

          • Incoming and Outgoing conversations with Attachment name and location

            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", LONGTODATE (Conv.createdtime) "Incoming ...
          • Query to show total number of calls received and resolved per month

            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  LEFT JOIN WorkOrderStates wos ON ...
          • Query to show Total Onhold time_PGSQL

            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", cd.CATEGORYNAME AS "Category", ...
          • Query to show total time taken to resolve and total time onhold _ MSSQL

            MSSQL: SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "qd"."QUEUENAME" AS "Group", "aau"."FIRST_NAME" AS "Requester", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", "urgdef"."NAME" AS "Urgency", ...
          • Query to show total time spent for a ticket

            PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS "Subject", rtdef.NAME AS "Request Type", ti.FIRST_NAME AS "Technician", sdo.NAME AS "Site", ...