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

MSSQL:

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

          • Related Articles

          • Query to show Total Onhold time_PGSQL

            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", cd.CATEGORYNAME AS "Category", ...
          • Query to show when Onhold was placed for a request

            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 "Completed Time", ...
          • Query to show Time spent in each status - Time Analysis

            MSSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject",queuedefinition.queuename "Support Group", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...
          • Query to show the time taken between created time and first technician assigned time _PGSQL

            Go to Reports-New Query Report and execute this query. PGSQL: SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",LONGTODATE(wo.createdtime) "Created Time", LONGTODATE(wo.COMPLETEDTIME) "Completed Time", LONGTODATE(MIN(woh.OPERATIONTIME)) ...
          • Query to show unassigned time of a request and when its first assigned

            Go to Reports-New Query Report and execute this query. MSSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", LONGTODATE(wo.createdtime) "Created Time",  LONGTODATE(wo.COMPLETEDTIME) "Completed Time", ...