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 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.
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 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 technician created time (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT AaaUser.FIRST_NAME "Technician Name", aci.emailid "Email ID", LONGTODATE(AaaUser.createdtime) "Technician Created Time", SDUSER.STATUS "Status(Active/Inactive)" FROM AaaUser left ...Query to display additional field associations with templates (MSSQL)
Tested in build MSSQL (14306) 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 ...Query to show project tasks status wise count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT projectdet.title AS "Project name", count(case when (taskstatus.statusid='1') THEN 1 ELSE NULL END) "Open Tasks", count(case when (taskstatus.statusid='6') THEN 6 ELSE NULL END) "In ...