Query to show number of tickets created per template with their status

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 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.
          • Related Articles

          • 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 ...
          • 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 ...
          • Query to show Approved tickets per Approver

            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 "Approver Email", asd.stagename "Stage", ...
          • 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 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", ...