Survey responses for the past 7 days with response time filtered.
SELECT wo.WORKORDERID AS "Request ID",
wo.TITLE AS "Request Title",
ad.org_name AS "Account",
srm.RESULT AS "Rating %",
ti.FIRST_NAME AS "Technician",
longtodate(srm.responsetime) AS "Survey Responded Date",
srcmt.COMMENTTEXT AS "Comments",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 5) "(1)Was the request addressed to your satisfaction?",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 6) "(2)How satisfied are you with the responsiveness of Nayo Technician?",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 7) "(3)How would you rate the quality of the given solution?",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 8) "(4)How would you rate the duration it took to address your request?",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 9) "(5)How satisfied are you with the overall service you received?",
case when srm.responsetime > 0 then aau.FIRST_NAME else null END AS "Survey Answered By" FROM SurveyResponseRequestMapping srrm
INNER JOIN Survey_Response_Main srm ON srrm.RESPONSEID=srm.RESPONSEID
LEFT JOIN Survey_Response_Comment srcmt ON srm.RESPONSEID=srcmt.RESPONSEID
LEFT JOIN Survey_Main smn ON srm.SURVEYID=smn.SURVEYID
LEFT JOIN WorkOrder wo ON srrm.WORKORDERID=wo.WORKORDERID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN AaaLogin ON aau.USER_ID = AaaLogin.USER_ID
left join accountsitemapping asm on asm.siteid = wo.siteid
Left join accountdefinition ad on asm.accountid = ad.org_id
LEFT JOIN WorkOrderStates wos ON wo.workorderid=wos.workorderid
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
where qd.queuename='TS-Technical Services' and extract(epoch FROM(now()::TIMESTAMP - to_timestamp(srm.responsetime/1000)::TIMESTAMP))/3600/24 < 7;
wo.TITLE AS "Request Title",
ad.org_name AS "Account",
srm.RESULT AS "Rating %",
ti.FIRST_NAME AS "Technician",
longtodate(srm.responsetime) AS "Survey Responded Date",
srcmt.COMMENTTEXT AS "Comments",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 5) "(1)Was the request addressed to your satisfaction?",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 6) "(2)How satisfied are you with the responsiveness of Nayo Technician?",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 7) "(3)How would you rate the quality of the given solution?",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 8) "(4)How would you rate the duration it took to address your request?",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 9) "(5)How satisfied are you with the overall service you received?",
case when srm.responsetime > 0 then aau.FIRST_NAME else null END AS "Survey Answered By" FROM SurveyResponseRequestMapping srrm
INNER JOIN Survey_Response_Main srm ON srrm.RESPONSEID=srm.RESPONSEID
LEFT JOIN Survey_Response_Comment srcmt ON srm.RESPONSEID=srcmt.RESPONSEID
LEFT JOIN Survey_Main smn ON srm.SURVEYID=smn.SURVEYID
LEFT JOIN WorkOrder wo ON srrm.WORKORDERID=wo.WORKORDERID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN AaaLogin ON aau.USER_ID = AaaLogin.USER_ID
left join accountsitemapping asm on asm.siteid = wo.siteid
Left join accountdefinition ad on asm.accountid = ad.org_id
LEFT JOIN WorkOrderStates wos ON wo.workorderid=wos.workorderid
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
where qd.queuename='TS-Technical Services' and extract(epoch FROM(now()::TIMESTAMP - to_timestamp(srm.responsetime/1000)::TIMESTAMP))/3600/24 < 7;
New to ADSelfService Plus?
Related Articles
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", ...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. ...Qyery to bring Survey reports based on technicians , tickets, satisfactory level (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) 1. Login to SDP MSP as administrator 2. Execute this from SDP MSP application -> Reports -> New Query report select srrm.workorderid AS "Ticket Number",aau.FIRST_NAME as "Requester Name",srm.RESULT as ...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 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 ...