Query to show total number of calls received and resolved per month

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 wo.WORKORDERID=wos.WORKORDERID  LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID  LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID  LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID  
WHERE (wo.ISPARENT='1')  and wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth>
order by 1

Received, Resolved and Closed:

SELECT 
count(wo.workorderid) "Total number of calls received" ,
count(case when wo.resolvedtime >= <from_thismonth> AND wo.completedtime <= <to_thismonth> THEN 1 ELSE NULL END)  "Total number of calls Resolved",
count(case when wo.completedtime >= <from_thismonth> AND wo.completedtime <= <to_thismonth> THEN 1 ELSE NULL END)  "Total number of calls Closed" 
 FROM WorkOrder wo  LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID  LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID  LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID  LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID  
WHERE (wo.ISPARENT='1')  and wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth>
order by 1

NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.

How to compare date column with auto filled date templates?
  1. Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
    • <from_thisweek> - Starting date of this week
    • <to_thisweek> - Ending date of this week
  2. Available Date Templates
    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>
          • Related Articles

          • Query to show Approved tickets per Approver

            PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", std.STATUSNAME "Request Status", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", ApprovalDetails.EMAIL "Approver Email", asd.stagename "Stage", ...
          • Query to show Created, Pending and Completed Requests

            PGSQL & MSSQL: SELECT  count(wo.workorderid) "Total Created Last Week", count(case when std.ispending='1' THEN 1 ELSE NULL END) "Pending from Last Week",  count(case when wo.completedtime >= <from_lastweek> AND wo.completedtime <= <to_lastweek> THEN ...
          • 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 ...
          • Query report to show Open requests without open tasks

            PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...
          • Query to show Logged, Resolved and Backlog tickets based on a Region

            SELECT rd.REGIONNAME "Region",  count(wo.workorderid) "Logged",   count(case when std.ispending='0' THEN 1 ELSE NULL END) "Resolved", count(case when std.ispending='1' THEN 1 ELSE NULL END) "Backlog"  FROM WorkOrder wo LEFT JOIN WorkOrder_Queue woq ...