Solved tickets with survey results

Hi,

We want to receive a weekly inline HTML mail with all our solved tickets of that week with their survey results and SLA Time Exceeded.

Where it needs to be sorted by “ Priority ” and sorted by age – from old to new – at “ SLA Time Exceeded ”.
We currently use the query below.

SELECT sum(sadt.RATING)/(count(sadt.RATING)*(select count(SURANSID) from surveyanswers))*100 "% Rating" ,

max(spre.COMMENTS) "Comments",

max(std.statusname) "Status",

wo.WORKORDERID "Request ID",

max(pd.PRIORITYNAME) "Priority",

dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (max(wo.CREATEDTIME)/1000),'1970-01-01 00:00:00') "Request Created Time",

dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (max(wo.DUEBYTIME)/1000),'1970-01-01 00:00:00') "Request Due Time",

CAST((((max(wo.DUEBYTIME)-max(wo.completedtime))/1000)/(3600*24)) AS VARCHAR(10))+'days '+CAST((((max(wo.DUEBYTIME)-max(wo.completedtime))/1000)/3600)%24 AS VARCHAR(10))+'hours '+CAST((((max(wo.DUEBYTIME)-max(wo.completedtime))/1000)/60)%60 AS VARCHAR(10))+'min '+CAST(((max(wo.DUEBYTIME)-max(wo.completedtime))/(1000))%60 AS VARCHAR(10))+'sec' "SLA" 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 StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID where wos.isoverdue='1' group by wo.WORKORDERID order by 5

But we want that it only shows the past month. Right now it shows all survey results.

FYI:

Database: Microsoft SQL (MSSQL)

ME SDP: Version is 9.1 and it is build 9100

Thank you in advance!

Kind Regards,

Rick