Number of open and closed tasks per ticket

Number of open and closed tasks per ticket



MSSQL:

SELECT "wo"."WORKORDERID" AS "Request ID", "qd"."QUEUENAME" AS "Group", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", "icd"."NAME" AS "Item", "wo"."TITLE" AS "Subject", "ti"."FIRST_NAME" AS "Technician", "sdo"."NAME" AS "Site", "std"."STATUSNAME" AS "Request Status", LONGTODATE("wo"."CREATEDTIME") AS "Created Time", LONGTODATE("wo"."DUEBYTIME") AS "DueBy Time", "wos"."ISOVERDUE" AS "Overdue Status",sum(case when tsd.statusid=1 then 1 else 0 end)"Count Of Open Tasks",sum(case when  tsd.statusid=3 then 1 else 0 end) 'Count Of Closed Tasks' FROM "WorkOrder" "wo" LEFT JOIN "SDUser" "sdu" ON "wo"."REQUESTERID"="sdu"."USERID" LEFT JOIN "AaaUser" "aau" ON "sdu"."USERID"="aau"."USER_ID" LEFT JOIN "DepartmentDefinition" "dpt" ON "wo"."DEPTID"="dpt"."DEPTID" LEFT JOIN "SiteDefinition" "siteDef" ON "wo"."SITEID"="siteDef"."SITEID" LEFT JOIN "SDOrganization" "sdo" ON "siteDef"."SITEID"="sdo"."ORG_ID" LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID" LEFT JOIN "CategoryDefinition" "cd" ON "wos"."CATEGORYID"="cd"."CATEGORYID" LEFT JOIN "SubCategoryDefinition" "scd" ON "wos"."SUBCATEGORYID"="scd"."SUBCATEGORYID" LEFT JOIN "ItemDefinition" "icd" ON "wos"."ITEMID"="icd"."ITEMID" LEFT JOIN "SDUser" "td" ON "wos"."OWNERID"="td"."USERID" LEFT JOIN "AaaUser" "ti" ON "td"."USERID"="ti"."USER_ID" LEFT JOIN "StatusDefinition" "std" ON "wos"."STATUSID"="std"."STATUSID" LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID" LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID" LEFT JOIN Workordertotaskdetails wtd ON wo.workorderid=wtd.workorderid LEFT JOIN Taskdetails tsd ON wtd.taskid=tsd.taskid GROUP BY wo.workorderid,Qd.QUEUENAME,aau.FIRST_NAME,dpt.DEPTNAME,Cd.CATEGORYNAME,scd.NAME,icd.NAME,wo.TITLE,ti.FIRST_NAME,sdo.NAME,std.STATUSNAME,wo.CREATEDTIME, wo.DUEBYTIME,wos.ISOVERDUE



          • Related Articles

          • 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 ...
          • 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 total number of calls received and resolved per month

            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  LEFT JOIN WorkOrderStates wos ON ...
          • Query to show project tasks status wise count

            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 Progress Tasks", count(case when ...
          • Difference between resolved and Closed notification

            The requester notifications are sent for two status: 'Resolved' and 'Closed' ​ Both 'Resolved' and 'Closed' are default completed statuses. When a technician provides a solution that he thinks will solve the issue, he/she can set the request to the ...