Update Report

Hello,

I am using this report to generate list of tickets that haven't been updated in greater than 5 days.

select wo.WORKORDERID"Request ID",

aau.FIRST_NAME "Requester",

wo.TITLE "Subject",

ti.FIRST_NAME "Technician",

LONGTODATE(wo.CREATEDTIME) "Created Time" 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 DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID

LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID

LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID

LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_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 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 StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (std.ispending = '1') and (not(std.statusname= 'project' or std.statusname= 'problem')) AND wo.ISPARENT='1' AND DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.createdtime/1000),'1970-01-01 00:00:00'),GETDATE()) > 0 and DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wos.LAST_TECH_UPDATE/1000),'1970-01-01 00:00:00'),GETDATE()) > 5

I'd like to modify it so I can filter by technician and/or department. The goal is to schedule this so send daily to the filtered technician so they can see what tickets need to be updated.

I'd like a >7 day report, by department, which will come to me to see what tickets are not being updated.

Thanks