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", wo.TITLE AS "Subject", rtdef.NAME AS "Request Type", ti.FIRST_NAME AS "Technician", pd.PRIORITYNAME AS "Priority", ad.ORG_NAME AS "Account", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date", (wo.RESPONDEDTIME/(1000*60)) AS "Response time in mins", LONGTODATE(wo.COMPLETEDTIME) AS "Completed Time", wos.IS_FR_OVERDUE AS "First Response Overdue Status" FROM WorkOrder wo LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID=wam.WORKORDERID LEFT JOIN PortalAccounts port_acc ON wam.ACCOUNTID=port_acc.ACCOUNTID LEFT JOIN AccountDefinition ad ON port_acc.ACCOUNTID=ad.ORG_ID 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 SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID WHERE (wo.ISPARENT='1')
OUTPUT:
The query returns the below columns,
New to ADSelfService Plus?
Related Articles
Time taken to First Response
First Response Time is the number of minutes, hours, or days between when a customer submits a support ticket and when a customer support representative provides an initial response. It indicates how long a customer has to wait before being helped. ...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 report for Time Spent for First response in Version 11.0 (Postgres)
SELECT slad.SLANAME AS "Request SLA", wo.WORKORDERID AS "Request ID", longtodate(wo.CREATEDTIME) AS "Created Time", slad.SLANAME AS "Request SLA", longtodate(wo.FR_DUETIME) AS "Request first response due time",round(wo.RESPONSE_TIMESPENT / 60000) AS ...Query to show the time taken between created time and first technician assigned time (PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 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) ...Query to get total worklog time spent on a request. (PGSQL)
Tested in build PGSQL (14300) Scenario: Request1 worklog1-Total time taken to resolve=1 hr 20 mins. worklog2- Total time taken to resolve=1hr 10mins. While generating a report the total time taken to resolve for request1 should be 2 hrs 30mins. ...