Request Management - overview of all conversations of technician and requester in a request
This report gives an overview of all conversations of technician and requester in a request.
SELECT wo.WORKORDERID "Request ID", (aau.FIRST_NAME) "Requester", (wo.TITLE) "Subject", (ti.FIRST_NAME) "Technician", longtodate(wo.CREATEDTIME) "Created Time", longtodate(c.createdtime)"Conversation CreatedOn", c.title "Conversation title", longtodate(n.notificationdate)"Technician reply CreatedOn", c.first_name "conversation by", c.description "conversation Description", n.notificationtitle "NOtification title", n.description "Notification Description", n.first_name "Sender name" FROM WorkOrder wo 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 LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN (SELECT con.workorderid, con.title, con.createdtime, au1.first_name, cond.description FROM conversation con LEFT JOIN aaauser au1 ON con.requesterid=au1.user_id LEFT JOIN conversationdescription cond ON con.CONVERSATIONID= cond.CONVERSATIONID) c ON c.workorderid=wo.workorderid LEFT JOIN (SELECT nw.workorderid, no.notificationtitle, no.notificationdate, au2.first_name, nod.description FROM notify_workorder nw LEFT JOIN notification NO ON nw.notificationid=no.notificationid LEFT JOIN aaauser au2 ON no.senderid=au2.user_id LEFT JOIN notificationtodesc nod ON no.NOTIFICATIONID=nod.NOTIFICATIONID WHERE no.senderid !=1) n ON n.workorderid=wo.workorderid WHERE wo.CREATEDTIME >= <from_thisweek> and wo.createdtime <= <to_thisweek>