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 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 created during weekends (Saturdays and Sundays)

            MSSQL: SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", "icd"."NAME" AS "Item", ...
          • 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 that lists pending approval of a particular service approver:

            PGSQL: SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE "Subject",ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time",appsau.first_name "Approver", asd.statusname "Approval Status" FROM WorkOrder_Threaded wot ...
          • Query to show active and archived tickets

            Below is only for MSSQL. For PGSQL query, refer comments section SELECT wo.WORKORDERID "Request ID",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",serdef.NAME "Service Category",wo.TITLE "Subject",sdo.NAME "Site",accountdefinition.org_name ...