Query to show Created, Pending and Completed Requests

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 1 ELSE NULL END) "Closed Last Week" 
 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_lastweek> AND wo.CREATEDTIME <= <to_lastweek>
order by 1

Total received, pending and closed:

SELECT 
count(wo.workorderid) "Total Received",
count(case when std.ispending='1' THEN 1 ELSE NULL END) "Tickets Pending", 
count(case when std.ispending='0' THEN 1 ELSE NULL END) "Tickets 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_thisyear>
  AND wo.CREATEDTIME <= <to_thisyear>
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 number of tickets created per template with their status

            SELECT reqtl.TEMPLATENAME "Request Template",   count(wo.WORKORDERID) "Request Count", count(case when std.ispending='0' THEN 1 ELSE NULL END) "Completed", count(case when std.ispending='1' THEN 1 ELSE NULL END) "Pending"  FROM WorkOrder wo LEFT JOIN ...
          • Query to show Count of tickets based on OP Hrs_ MSSQL

            MSSQL: OP Hrs: (8.30 - 18.30) SELECT  qd.queuename "Group", "pd"."PRIORITYNAME" AS "Priority", ad.org_name "Account", count(wo.workorderid) "Total Tickets"  FROM WorkOrder wo LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN ...
          • Query to show tickets created based on shift time_PGSQL

            Filter mentioned by converting into minutes. PGSQL: 7.30 AM - 7.30 PM SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) AS "Created Time", extract(hour from ...
          • Query to show tickets first assigned today irrespective of created date

            MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request Status", ...
          • Query to show tasks created per template along with status

            Since Task template is not stored in Task details, we cannot show count based on task templates. However, we have tried to form a query with task subject considering that, when task templates are used, the subject are not altered.  SELECT ...