Query to show total number of calls received and resolved per month (MSSQL & PGSQL )

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  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>

Last tested on Builds: 14500 and above



                  New to ADSelfService Plus?