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
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 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 ticket first assign/pick up time _ (PGSQL )
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject", longtodate(wo.CREATEDTIME) "Request Created Time", longtodate(woh.operationtime) "First Pickup time", qd.QUEUENAME "Group",ti.FIRST_NAME ...