Query to show all request along with a column to show survey completed or not. (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306)
Please go to Reports > New Query Reports and execute the below query (MS-SQL & PGSQL):
SELECT wo.WORKORDERID AS "Request ID",
max(wo.TITLE) AS "Request Title",
max(aau.FIRST_NAME) AS "Requester",
max(ti.FIRST_NAME) AS "Technician",
longtodate(max(wo.completedtime)) AS "Completed Time",
CASE WHEN max(srm.Responsetime) IS NOT NULL THEN 'TRUE' ELSE 'FALSE' END "Survey Attended ?",
longtodate(max(srm.Responsetime)) AS "Survey responded Time",
max(srm.result) "Survey %" FROM WorkOrder wo
LEFT JOIN SurveyResponseRequestMapping srrm ON srrm.WORKORDERID=wo.WORKORDERID
LEFT JOIN Survey_Response_Main srm ON srrm.RESPONSEID=srm.RESPONSEID
LEFT JOIN Survey_Response_Answer sra ON srm.RESPONSEID=sra.RESPONSEID
LEFT JOIN Survey_Question sq ON sra.QUESID=sq.QUESID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
where wo. completedtime >= <from_yesterday> AND wo. completedtime <= <to_yesterday>
GROUP BY wo.WORKORDERID
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 request details along with technician's and requester's department (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Along with technician's department: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS ...Query to show ticket aging - PGSQL
Last tested on 14500 Database: PGSQL: select wo.workorderid as "RequestID", modedefinition.modename "Request Mode", queuedefinition.queuename "Group", cd.CategoryName as "Category", scd.name as "SubCategory", itemdefinition.name "Item", ...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 Created, Pending and Completed Requests ( PGSQL & MSSQL )
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT count(wo.workorderid) "Total Created Last Week", count(case when std.ispending='1' THEN 1 ELSE NULL END) "Pending from Last Week", count(case when wo.completedtime >= ...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 ...