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 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 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 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 count of tickets in each module per technician (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute this query. SELECT 'Request' "Module",aaau.first_name "Technician",count(wo.WORKORDERID) "Request count" FROM WorkOrder wo left join workorderstates wos ON ...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 ...