Query to get the Resolution overdue time and Response overdue time for each ticket.
Tested in 14620
Query:
SELECT
ad.ORG_NAME "Account",
wo.WORKORDERID "Ticket ID",
rtdef.NAME "Request Type",
wo.RESOLVEDTIME "Resolved Time",
pd.PRIORITYNAME "Priority",
(
CASE WHEN wos.IS_FR_OVERDUE = '1' THEN
(
CASE WHEN wo.RESPONDEDTIME = 0 THEN
CONVERT(VARCHAR(20), FLOOR((DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', getDate()) - wo.FR_DUETIME ) / (1000 * 60 * 60)))
+ ':' +
CONVERT(VARCHAR(20), FLOOR((DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', getDate()) - wo.FR_DUETIME ) / (1000 * 60))%60)
ELSE
CONVERT(VARCHAR(20), FLOOR(( wo.RESPONDEDTIME - wo.FR_DUETIME ) / (1000 * 60 * 60)) )
+ ':' +
CONVERT(VARCHAR(20),FLOOR((( wo.RESPONDEDTIME - wo.FR_DUETIME ) / (1000 * 60)) % 60))
END
)
ELSE '0'
END
) AS "Response overdue time",
(
CASE WHEN wos.ISOVERDUE = '1' THEN
(
CASE WHEN wo.RESOLVEDTIME = 0 THEN
CONVERT(VARCHAR(20), FLOOR((DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', getDate()) - wo.DUEBYTIME ) / (1000 * 60 * 60)))
+ ':' +
CONVERT(VARCHAR(20),FLOOR((DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', getDate()) - wo.DUEBYTIME ) / (1000 * 60))%60)
ELSE
CONVERT(VARCHAR(20), FLOOR(( wo.RESOLVEDTIME - wo.DUEBYTIME ) / (1000 * 60 * 60)) )
+ ':' +
CONVERT(VARCHAR(20),FLOOR(( ( wo.RESOLVEDTIME - wo.DUEBYTIME ) ) / (1000 * 60))%60)
END
)
ELSE '0'
END
) AS "Resolution overdue time"
FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID
LEFT JOIN PriorityDefinitiON pd ON wos.PRIORITYID = pd.PRIORITYID
LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID = wam.WORKORDERID
LEFT JOIN AccountDefinitiON ad ON wam.ACCOUNTID = ad.ORG_ID
LEFT JOIN RequestTypeDefinitiON rtdef ON wos.REQUESTTYPEID = rtdef.REQUESTTYPEID
WHERE wo.ISPARENT = '1'
New to ADSelfService Plus?
Related Articles
Query to get the MTTR (Mean Time To Respond & Mean Time To Resolve) reports
MTT Response Report is calculated as below, >> Response Time: Ticket Responded Time - Ticket Created Time >> MTT Response: Average total response time within a given time frame = Total Response Time within a given time frame (Divided by) Number of ...Query to retrieve response time in minutes
To return the ticket response time in minutes along with other request details. TESTED IN: Builds 14700 (Postgres) QUERY: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", ...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 ...Average Resolution Time based on Technicians
This report is used for the customers that Technicians provide a consistent level of support. It can be confusing and frustrating for customers to have some Technicians resolve quickly, while other Technicians takes days to resolve the issues and ...Average Resolution Time based on Group
This report is used for the customers that support groups provide a consistent level of support. It can be confusing and frustrating for customers to have some support groups resolve quickly, while other support groups takes days to resolve the ...