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 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 ...
          • 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 who closed the request

            PGSQL & MSSQL: select wo.workorderid "Request ID", aau.first_name "Request Closed By", cit.typename "Role", queuedefinition.queuename "Group" from WorkOrder wo  LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID LEFT JOIN ...
          • Query to show the number of days, the tickets are open_PGSQL

            select wo.WORKORDERID"Request ID", qd.QUEUENAME "Group", aau.FIRST_NAME "Requester", wo.TITLE "Subject", ti.FIRST_NAME "Technician", sdo.NAME "Site", LONGTODATE(wo.CREATEDTIME) "Created Time", round(extract(epoch from(now()::TIMESTAMP - ...