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 "satisfaction level",sd.FIRST_NAME as "Technician Name" from SurveyResponseRequestMapping srrm
left join Survey_Response_Main srm ON srrm.RESPONSEID=srm.RESPONSEID
LEFT JOIN AaaUser aau ON srm.USERID=aau.USER_ID
left join WorkOrderStates wos ON srrm.WORKORDERID=wos.WORKORDERID
LEFT JOIN AaaUser sd ON wos.OWNERID=sd.USER_ID
where srm.RESULT IS NOT NULL
==============
Other format
select srrm.workorderid AS "Ticket Number",srm.RESULT as "satisfaction level (%)", aau.FIRST_NAME as "Requester Name",sd.FIRST_NAME as "Technician Name",longtodate(srm.RESPONSETIME) as "Survey Responded time", "ad"."ORG_NAME" AS "Account" from SurveyResponseRequestMapping srrm
left join Survey_Response_Main srm ON srrm.RESPONSEID=srm.RESPONSEID LEFT JOIN AaaUser aau ON srm.USERID=aau.USER_ID left join WorkOrderStates wos ON srrm.WORKORDERID=wos.WORKORDERID left join WorkOrder wo ON srrm.WORKORDERID=wo.WORKORDERID LEFT JOIN AaaUser sd ON wos.OWNERID=sd.USER_ID
Left join AccountSiteMapping asm ON wo.SITEID=asm.SITEID Left JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID where srm.RESULT IS NOT NULL AND
srm.RESPONSETIME >= datetolong('2021-04-20') AND srm.RESPONSETIME <= datetolong('2021-04-23')
With Survey comments:
select srrm.workorderid AS "Ticket Number",srm.RESULT as "satisfaction level (%)", srcmt.COMMENTTEXT AS "Comments", aau.FIRST_NAME as "Requester Name",sd.FIRST_NAME as "Technician Name",longtodate(srm.RESPONSETIME) as "Survey Given date", ad.ORG_NAME AS "Requester Company" from SurveyResponseRequestMapping srrm
left join Survey_Response_Main srm ON srrm.RESPONSEID=srm.RESPONSEID LEFT JOIN Survey_Response_Comment srcmt ON srm.RESPONSEID=srcmt.RESPONSEID LEFT JOIN AaaUser aau ON srm.USERID=aau.USER_ID left join WorkOrderStates wos ON srrm.WORKORDERID=wos.WORKORDERID left join WorkOrder wo ON srrm.WORKORDERID=wo.WORKORDERID LEFT JOIN AaaUser sd ON wos.OWNERID=sd.USER_ID
Left join AccountSiteMapping asm ON wo.SITEID=asm.SITEID Left JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID where srm.RESULT IS NOT NULL AND srm.RESPONSETIME >= datetolong('2021-01-20') AND srm.RESPONSETIME <= datetolong('2021-05-23')
PS : Add criteria for dates and other under where condition
NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.
How to compare date column with auto filled date templates?
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- Available Date Templates
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
New to ADSelfService Plus?
Related Articles
Query to find deleted survey (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Go to Reports >> New Query Report >> Run the below query to get the data SELECT err.message "System log message", err.errormodule "Module", err.suberrormodule "Sub Module", err.action "Action", ...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 ...How to calculate satisfactory level for Survey
We are considering the rating value (Score) in the calculation of the survey Example : Suppose the Survey has 7 Questions with maximum of 10 Radio type points and each points carry above mentioned score. If customer clicks 9 for all 7 questions (that ...Query to show KPI report based on technician (PGSQL & MSSQL)
Working on Builds: 14500 Database: MSSQL, PGSQL This report is already available under Frequently asked reports under Reports->New Query Reports page. Below query can be used for date filter and filter by technicians name modifications. SELECT ...Query to show Logged, Resolved and Backlog tickets based on a Region (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT rd.REGIONNAME "Region", count(wo.workorderid) "Logged", count(case when std.ispending='0' THEN 1 ELSE NULL END) "Resolved", count(case when std.ispending='1' THEN 1 ELSE NULL END) "Backlog" FROM ...