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
New to ADSelfService Plus?
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 (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 ...Query to show Last added worklog of a ticket ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", ...Query to show tickets that shows total time spent of the ticket more than x hours (MSSQL)
Tested in MSSQL build (14306) Query to show tickets that shows total time spent of the ticket more than 8 hours: select ad.org_name "Account", sdo.name "Site", wo.workorderid "Request ID", au.first_name "Assigned Technician", ...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 ...