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 SLA response and resolution success percentage
Last Tested on builds 14500 Databases: PGSQL & MSSQL: SELECT pd.PRIORITYNAME AS "Priority" , case when count(wo.workorderid) > 0 then cast(count(case when (wos.IS_FR_OVERDUE='0') THEN 1 ELSE NULL END) *100 as float) / cast(count(wo.workorderid) as ...Query to show Average resolution time for Category (PGSQL)
Tested in PGSQL build (14300) Databases: PGSQL SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.resolvedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg resolution Time" FROM ...Query to show response, resolution SLA violated count and its percentage ( PGSQL & MSSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT pd.PRIORITYNAME AS "Priority" , count(wo.WORKORDERID) "Total Requests", count(case when ( std.ispending='0') THEN 1 ELSE NULL END) "Closed", count(case when (wos.IS_FR_OVERDUE='1') THEN 1 ELSE ...