Query to show the survey sent and received count( PGSQL & MSSQL )
Tested in Build PGSQL (14300) or MSSQL (14306)
Go To Reports- New Query Report and execute this query.
select adef.org_name "Account",count(srm.responseid) as "Total Survey Sent",count( CASE WHEN srm.responsetype!=3 then 1 ELSE NULL END) as "Total Survey received" from survey_response_main srm LEFT JOIN SurveyResponseRequestMapping srrm ON srm.responseid=srrm.responseid LEFT JOIN workorder wo ON wo.workorderid=srrm.workorderid LEFT JOIN accountsitemapping asm on asm.siteid=wo.siteid
LEFT JOIN accountdefinition adef on adef.org_id=asm.accountid
where wo.completedtime >= <from_thisyear> AND wo.completedtime <= <to_thisyear> and adef.org_id in ($Account)
group by adef.org_name order by 1
New to ADSelfService Plus?
Related Articles
Query to show Count of survey sent and received per account (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT adef.org_name "Account",COUNT(srm.surveyid) "Total surveys sent", COUNT (sra.responseid) "Total surveys received" FROM SurveyResponseRequestMapping srrm INNER JOIN Survey_Response_Main srm ON ...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 technician hop count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester", ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ...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 total number of calls received and resolved per month (MSSQL & PGSQL )
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT count(wo.workorderid) "Total Created", count(case when wo.completedtime >= <from_thismonth> AND wo.completedtime <= <to_thismonth> THEN 1 ELSE NULL END) "Closed" FROM WorkOrder wo ...