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 srrm.RESPONSEID=srm.RESPONSEID
LEFT JOIN Survey_Response_Answer sra ON srm.RESPONSEID=sra.RESPONSEID
LEFT JOIN WorkOrder wo ON srrm.WORKORDERID=wo.WORKORDERID
LEFT JOIN accountsitemapping asm on asm.siteid=wo.siteid
LEFT JOIN accountdefinition adef on adef.org_id=asm.accountid
where wo.completedtime >= <from_thisweek> AND wo.completedtime <= <to_thisweek>
group by adef.org_name order by 1
INNER JOIN Survey_Response_Main srm ON srrm.RESPONSEID=srm.RESPONSEID
LEFT JOIN Survey_Response_Answer sra ON srm.RESPONSEID=sra.RESPONSEID
LEFT JOIN WorkOrder wo ON srrm.WORKORDERID=wo.WORKORDERID
LEFT JOIN accountsitemapping asm on asm.siteid=wo.siteid
LEFT JOIN accountdefinition adef on adef.org_id=asm.accountid
where wo.completedtime >= <from_thisweek> AND wo.completedtime <= <to_thisweek>
group by adef.org_name order by 1
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 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 ...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 tasks created per template along with status (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Since Task template is not stored in Task details, we cannot show count based on task templates. However, we have tried to form a query with task subject considering that, when task templates are ...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 ...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 ...