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?
- 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
- 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 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 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 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", ...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 ...