SELECT wo.WORKORDERID AS "Request ID",
wo.TITLE AS "Request Title",
ad.org_name AS "Account",
srm.RESULT AS "Rating %",
ti.FIRST_NAME AS "Technician",
longtodate(srm.responsetime) AS "Survey Responded Date",
srcmt.COMMENTTEXT AS "Comments",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 5) "(1)Was the request addressed to your satisfaction?",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 6) "(2)How satisfied are you with the responsiveness of Nayo Technician?",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 7) "(3)How would you rate the quality of the given solution?",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 8) "(4)How would you rate the duration it took to address your request?",
(select sra.ANSWER from Survey_Response_Answer sra
LEFT JOIN Survey_Response_Main srm ON sra.responseid=srm.responseid
left JOIN surveyresponserequestmapping srrm ON srm.responseid=srrm.responseid
where srrm.WORKORDERID= wo.WORKORDERID and quesid = 9) "(5)How satisfied are you with the overall service you received?",
case when srm.responsetime > 0 then aau.FIRST_NAME else null END AS "Survey Answered By" FROM SurveyResponseRequestMapping srrm
INNER JOIN Survey_Response_Main srm ON srrm.RESPONSEID=srm.RESPONSEID
LEFT JOIN Survey_Response_Comment srcmt ON srm.RESPONSEID=srcmt.RESPONSEID
LEFT JOIN Survey_Main smn ON srm.SURVEYID=smn.SURVEYID
LEFT JOIN WorkOrder wo ON srrm.WORKORDERID=wo.WORKORDERID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN AaaLogin ON aau.USER_ID = AaaLogin.USER_ID
left join accountsitemapping asm on asm.siteid = wo.siteid
Left join accountdefinition ad on asm.accountid = ad.org_id
LEFT JOIN WorkOrderStates wos ON wo.workorderid=wos.workorderid
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
where qd.queuename='TS-Technical Services' and extract(epoch FROM(now()::TIMESTAMP - to_timestamp(srm.responsetime/1000)::TIMESTAMP))/3600/24 < 7;