Query to show Count of survey sent and received per account (MSSQL & PGSQL)

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

How to compare date column with auto filled date templates?
  1. 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
  2. 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?