Query to show total time taken to resolve and total time onhold ( MSSQL )

Query to show total time taken to resolve and total time onhold ( MSSQL )

Tested in build MSSQL (14306)

SELECT "wo"."WORKORDERID" AS "Request ID", "mdd"."MODENAME" AS "Request Mode", "qd"."QUEUENAME" AS "Group", "aau"."FIRST_NAME" AS "Requester", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", "urgdef"."NAME" AS "Urgency", "impdef"."NAME" AS "Impact", "rtdef"."NAME" AS "Request Type", "ti"."FIRST_NAME" AS "Technician", "pd"."PRIORITYNAME" AS "Priority", "std"."STATUSNAME" AS "Request Status", wof.UDF_CHAR4 "Resolution Method", wmf.UDF_CHAR10 "Major Incident", LONGTODATE(wo.CREATEDTIME) "Created Time", LONGTODATE(wo.RESPONDEDTIME) "Responded Date", LONGTODATE(wo.DUEBYTIME) "DueBy Time", LONGTODATE(wo.COMPLETEDTIME) "Completed Time", CASE WHEN wo.timespentonreq > 0 THEN CAST((((timespentonreq)/60000)%1440)/60 AS VARCHAR(20)) +':'+
CAST((((timespentonreq)/60000)%1440)%60 AS VARCHAR(20))  + ' ' ELSE 'Not Assigned' END AS 'Total Time taken to resolve(HH:MM)', (select cast(sum(wsi.timespent/60) as varchar(20)) +':'+cast(sum(wsi.timespent % 60) as varchar(20)) from
wo_assessment woa1
inner join wo_status_info wsi on woa1.assessmentid=wsi.assessmentid
left join statusdefinition sd1 on wsi.statusid=sd1.statusid where sd1.statusname like '%Onhold%' and wo.workorderid=woa1.workorderid) "Total Onhold Time (HH:MM)", "wos"."ISOVERDUE" AS "Overdue Status", "wos"."REOPENED" AS "ReOpened", "ad"."ORG_NAME" AS "Account", LONGTODATE(wo.RESOLVEDTIME) "Resolved Time", "sdu"."ISVIPUSER" AS "VIP User"  FROM "WorkOrder" "wo" LEFT JOIN "ModeDefinition" "mdd" ON "wo"."MODEID"="mdd"."MODEID" LEFT JOIN "SDUser" "sdu" ON "wo"."REQUESTERID"="sdu"."USERID" LEFT JOIN "AaaUser" "aau" ON "sdu"."USERID"="aau"."USER_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 "SDUser" "td" ON "wos"."OWNERID"="td"."USERID" LEFT JOIN "AaaUser" "ti" ON "td"."USERID"="ti"."USER_ID" LEFT JOIN "UrgencyDefinition" "urgdef" ON "wos"."URGENCYID"="urgdef"."URGENCYID" LEFT JOIN "ImpactDefinition" "impdef" ON "wos"."IMPACTID"="impdef"."IMPACTID" LEFT JOIN "RequestTypeDefinition" "rtdef" ON "wos"."REQUESTTYPEID"="rtdef"."REQUESTTYPEID" LEFT JOIN "PriorityDefinition" "pd" ON "wos"."PRIORITYID"="pd"."PRIORITYID" 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 "RequestResolver" "rrr" ON "wo"."WORKORDERID"="rrr"."REQUESTID" LEFT JOIN "RequestResolution" "rrs" ON "rrr"."REQUESTID"="rrs"."REQUESTID" LEFT JOIN "AccountSiteMapping" "asm" ON "wo"."SITEID"="asm"."SITEID" LEFT JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID" LEFT JOIN Workorder_fields wof ON wo.workorderid=wof.workorderid LEFT JOIN WorkOrder_Multi_Fields wmf ON wo.workorderid=wmf.workorderid WHERE (wo.ISPARENT='1')  AND wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth> GROUP BY wo.workorderid, mdd.MODENAME, qd.QUEUENAME, aau.FIRST_NAME, cd.CATEGORYNAME, scd.NAME, urgdef.NAME, impdef.NAME, rtdef.NAME, ti.FIRST_NAME, pd.PRIORITYNAME, std.STATUSNAME, wo.CREATEDTIME, wo.RESPONDEDTIME, wo.DUEBYTIME, wo.COMPLETEDTIME, wos.ISOVERDUE, wos.REOPENED, ad.ORG_NAME, wo.RESOLVEDTIME, sdu.ISVIPUSER, wof.UDF_CHAR4, wmf.UDF_CHAR10,Wo.TIMESPENTONREQ


                New to ADManager Plus?

                  New to ADSelfService Plus?

                    • Related Articles

                    • Query to show Total Onhold time ( PGSQL )

                      Tested in build PGSQL (14300) PGSQL: SELECT LONGTODATE(wo.CREATEDTIME) AS "Created Time", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", ...
                    • 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 when Onhold was placed for a request (PGSQL)

                      Tested in Build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID AS "Request ID", sdo.NAME AS "Site", cd.CATEGORYNAME AS "Category", longtodate(wo.CREATEDTIME) AS "Created Time", std.STATUSNAME AS "Current Request Status", longtodate(wo.COMPLETEDTIME) AS ...
                    • Query to show Average response time for Category (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...
                    • Query to show total time spent for a ticket ( PGSQL)

                      Tested in PGSQL build (14300) Database: PGSQL SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS "Subject", rtdef.NAME AS "Request Type", ti.FIRST_NAME AS ...