All open tickets with notes fields
Good afternoon. I have the query below which works great. It shows all open tickets that have notes. I am trying to modify this report to show all open tickets, whether or not they have notes, but want to retain the note-related fields. Any help would be appreciated.
SELECT wo.WORKORDERID 'Request ID',wo.TITLE 'Subject',aau.FIRST_NAME 'Requester',ti.FIRST_NAME 'Technician',qd.QUEUENAME 'Group',rtdef.NAME 'Request Type',std.STATUSNAME 'Request Status',DATE_FORMAT(FROM_UNIXTIME(wo.CREATEDTIME/1000),'%d-%m-%Y %k:%i') 'Created Time',ns.notestext'Notes',DATE_FORMAT(FROM_UNIXTIME(ns.NOTESDATE/1000),'%d-%m-%Y %k:%i') 'Last Notes Updated Time',au.FIRST_NAME 'Last Notes Updated By' FROM WorkOrder wo LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 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 RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN Notes ns ON ns.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sd ON ns.USERID=sd.USERID LEFT JOIN Aaauser au ON au.USER_ID=sd.USERID WHERE ns.NOTESID = (SELECT MAX(nts.NOTESID) FROM Notes nts WHERE nts.WORKORDERID=ns.WORKORDERID)
SELECT wo.WORKORDERID 'Request ID',wo.TITLE 'Subject',aau.FIRST_NAME 'Requester',ti.FIRST_NAME 'Technician',qd.QUEUENAME 'Group',rtdef.NAME 'Request Type',std.STATUSNAME 'Request Status',DATE_FORMAT(FROM_UNIXTIME(wo.CREATEDTIME/1000),'%d-%m-%Y %k:%i') 'Created Time',ns.notestext'Notes',DATE_FORMAT(FROM_UNIXTIME(ns.NOTESDATE/1000),'%d-%m-%Y %k:%i') 'Last Notes Updated Time',au.FIRST_NAME 'Last Notes Updated By' FROM WorkOrder wo LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 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 RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN Notes ns ON ns.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sd ON ns.USERID=sd.USERID LEFT JOIN Aaauser au ON au.USER_ID=sd.USERID WHERE ns.NOTESID = (SELECT MAX(nts.NOTESID) FROM Notes nts WHERE nts.WORKORDERID=ns.WORKORDERID)