Request Management - last conversation of technician and requester in requests
This report returns the last conversation of technician and requester.
SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject",max(ti.FIRST_NAME) "Technician", longtodate(max(wo.CREATEDTIME)) "Created Time", CASE WHEN max(wos.notificationstatus) = 'REQ_REPLY' THEN max(c.description) WHEN max(wos.notificationstatus) = 'TECH_REPLY' THEN max(n.description) ELSE NULL END "Last conversation" 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,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, 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.NOTIFICATIONIDWHERE no.senderid !=1) n ON n.workorderid=wo.workorderid WHERE std.ispending='1' GROUP BY wo.WORKORDERID
To make any changes to this query, refer to this post.
Click this link to navigate to the next report.