Query to retrieve response time in minutes

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

                      • 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 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 Average response time for Technicians

                        PGSQL: SELECT aau.first_name "Technician", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg response Time" FROM workorder wo LEFT JOIN workorderstates wos ON wo.workorderid = wos.workorderid LEFT ...
                      • Query to retrieve the requests details

                        Tested in: 14610, 14301 QUERY: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date and Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date and Time", ...
                      • Query to retrieve both the live and archived requests details

                        Database: Pgsql Query: SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", id.NAME AS "Impact", ud.NAME AS "Urgency", rtd.NAME AS "Request Type", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved ...