Query to show Created, Pending and Completed Requests ( PGSQL & MSSQL )

Query to show Created, Pending and Completed Requests ( PGSQL & MSSQL )

Tested in build PGSQL (14300) and MSSQL (14306)

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>

                  New to ADSelfService Plus?

                    • Related Articles

                    • Query to show number of tickets created per template with their status (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) 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 ...
                    • Query to show active and archived tickets (MSSQL)

                      Tested in build MSSQL (14306) 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 "Account",aau.FIRST_NAME ...
                    • Query to show tickets created based on shift time ( PGSQL )

                      Tested in build PGSQL (14300) 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 ...
                    • Query to show tickets older than 30 days ( MSSQL )

                      Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", ...
                    • Query to show technician hop count (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester", ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ...