Number of open and closed tasks per ticket (MSSQL)

Number of open and closed tasks per ticket (MSSQL)

Tested in Build  MSSQL (14306)


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



                New to ADManager Plus?

                  New to ADSelfService Plus?