Query to show tasks created per template along with status

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 taskdet.TITLE AS "Task Title", count(wotask.WORKORDERID) AS "Count of tasks", 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 TaskDetails taskdet Inner JOIN WorkOrderToTaskDetails wototaskdet ON taskdet.TASKID=wototaskdet.TASKID LEFT JOIN WorkOrder wotask ON wototaskdet.WORKORDERID=wotask.WORKORDERID LEFT JOIN StatusDefinition std ON taskdet.STATUSID=std.STATUSID WHERE taskdet.TITLE='Check leasing' OR taskdet.TITLE='Check transport' OR taskdet.TITLE='Check power' group by taskdet.TITLE

Note: Task title names are mentioned in the query filter as highlighted, this can be modified as per need.

          • Related Articles

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

          • Query to display additional field associations with templates

            Use case The reports shows in which templates the created additional fields are associated  Query select sd.name "Service Catalog Name",            rt.templatename "Template name",            fc.field_name "Fields" from requesttemplate_list rt left ...

          • 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 request details along with technician's department

            PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group",ti.FIRST_NAME AS "Technician", dpt.DEPTNAME AS " Technician ...

          • Query report to show Open requests without open tasks

            PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...