Unable to query for survey results
We recently upgraded to 9422 on Nov 3 and are unable to query for our survey results after this date. We are able to view the scores in the "Survey Results" module under the "User Survey" section in the Admin tab. We are unsure where the disconnect is, we have been using the same query to report on survey scores for some time (out of an MSSQL database - query below).
select wo.WORKORDERID "Request_ID",
aau.FIRST_NAME "Requester",
ti.FIRST_NAME "Technician",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (spre.CREATEDTIME/1000),'1970-01-01 00:00:00') AS "Survey_Created_Time",
(sum(sadt.RATING)/(count(sadt.RATING)*(select count(SURANSID) from surveyanswers)))*100 "Rating Value",
COUNT(sadt.rating) AS "Questions Answered",
qd.QUEUENAME AS "Group"
FROM SurveyPerRequest spr
INNER JOIN SurveyPerRequestExt spre ON spr.SURVEYID=spre.SURVEYID LEFT JOIN SurveyDetails sdt ON spre.SURVEYID=sdt.SURVEYID
LEFT JOIN SurveyAnswers sadt ON sdt.SURANSID=sadt.SURANSID
LEFT JOIN WorkOrder wo ON spr.WORKORDERID=wo.WORKORDERID
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
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
WHERE (qd.QUEUENAME LIKE '%MTI%' OR qd.QUEUENAME LIKE '%MTA%') AND
(
(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (spre.CREATEDTIME/1000),'1970-01-01 00:00:00') >= '11-01-2018')
--AND ((dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (spre.CREATEDTIME/1000),'1970-01-01 00:00:00') <=@Stop))
)
and (aau.USER_ID <> ti.USER_ID)
GROUP BY qd.queuename,wo.workorderid,wo.TITLE,aau.FIRST_NAME,ti.FIRST_NAME,spre.CREATEDTIME
HAVING (COUNT(sadt.rating) = 5)
order by 4
select wo.WORKORDERID "Request_ID",
aau.FIRST_NAME "Requester",
ti.FIRST_NAME "Technician",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (spre.CREATEDTIME/1000),'1970-01-01 00:00:00') AS "Survey_Created_Time",
(sum(sadt.RATING)/(count(sadt.RATING)*(select count(SURANSID) from surveyanswers)))*100 "Rating Value",
COUNT(sadt.rating) AS "Questions Answered",
qd.QUEUENAME AS "Group"
FROM SurveyPerRequest spr
INNER JOIN SurveyPerRequestExt spre ON spr.SURVEYID=spre.SURVEYID LEFT JOIN SurveyDetails sdt ON spre.SURVEYID=sdt.SURVEYID
LEFT JOIN SurveyAnswers sadt ON sdt.SURANSID=sadt.SURANSID
LEFT JOIN WorkOrder wo ON spr.WORKORDERID=wo.WORKORDERID
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
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
WHERE (qd.QUEUENAME LIKE '%MTI%' OR qd.QUEUENAME LIKE '%MTA%') AND
(
(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (spre.CREATEDTIME/1000),'1970-01-01 00:00:00') >= '11-01-2018')
--AND ((dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (spre.CREATEDTIME/1000),'1970-01-01 00:00:00') <=@Stop))
)
and (aau.USER_ID <> ti.USER_ID)
GROUP BY qd.queuename,wo.workorderid,wo.TITLE,aau.FIRST_NAME,ti.FIRST_NAME,spre.CREATEDTIME
HAVING (COUNT(sadt.rating) = 5)
order by 4