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 NULL END) "Pending"
FROM WorkOrder wo
LEFT JOIN RequestTemplate_list reqtl ON wo.TEMPLATEID=reqtl.TEMPLATEID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
where reqtl.TEMPLATENAME='New site planning'
group by reqtl.TEMPLATENAME,reqtl.templateid
Note: Requeste template name is highlighted in this query, can be modified if needed.
New to ADSelfService Plus?
Related Articles
Query to show tasks created per template along with status (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) 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 ...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 >= ...Query to show Approved tickets per Approver (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", std.STATUSNAME "Request Status", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", ApprovalDetails.EMAIL ...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 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 ...