SELECT wo.WORKORDERID "Request ID",max(aau.FIRST_NAME) "Requester",max(dpt.DEPTNAME) "Department",max(rtdef.NAME) "Request Type",max(cd.CATEGORYNAME) "Category",max(scd.NAME) "Subcategory",max(wo.TITLE) "Subject",max(ti.FIRST_NAME) "Technician",longtodate(max(wo.DUEBYTIME))"DueBy Time",max(std.STATUSNAME) "Request Status",longtodate(max(nt.NOTIFICATIONDATE))'Last Notification date',
max(nt.NOTIFICATIONDESCRIPTION)'Last Notification',
(select longtodate(max(note.NOTESDATE)) from Notes note where note.WORKORDERID = wo.WORKORDERID)'Last note date',
(select (note1.NOTESTEXT) from notes note1 where note1.notesid in (select max(note.notesid) from Notes note group by note.workorderid) and note1.WORKORDERID = wo.WORKORDERID)'Last Notes' FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 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 Notify_WorkOrder nwo ON nwo.WORKORDERID = wo.WORKORDERID
LEFT JOIN Notification nt on nt.NOTIFICATIONID = (SELECT MAX(nwo.NOTIFICATIONID) FROM Notify_WorkOrder nwo WHERE nwo.WORKORDERID = wo.WORKORDERID) AND nt.senderid!=1
WHERE wo.ISPARENT='1' and std.ispending=1
and wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>
group by wo.workorderid