SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", sdo.NAME as "Site", LONGTODATE(Wo.createdtime) "Created Time", LONGTODATE(wos.last_tech_update) "Last Updated Time", ti1.FIRST_NAME "From technician",
ti2.FIRST_NAME "To technician", cast((wti.timespent/60) as varchar(20)) +':'+cast((wti.timespent % 60) as varchar(20)) "Time Spent by each technician", DATEDIFF(DAY, dateadd(s, datediff(s, GETUTCDATE(), getdate()) +(wo.createdtime/1000), '1970-01-01 00:00:00'), GETDATE()) "Days since created" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN AaaUser aau ON wo.REQUESTERID=aau.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN accountsitemapping asm on asm.siteid=sitedef.siteid LEFT JOIN accountdefinition adef on adef.org_id=asm.accountid LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 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 wo_assessment woa on wo.workorderid=woa.workorderid
inner join WO_TECH_INFO wti on woa.assessmentid=wti.assessmentid
LEFT JOIN AaaUser ti1 ON wti.technicianid=ti1.USER_ID
LEFT JOIN AaaUser ti2 ON wti.nexttechnicianid=ti2.USER_ID
WHERE wo.ISPARENT='1' and QUEUENAME= 'Customer Operations' AND std.STATUSNAME!= 'closed' and std.STATUSNAME!= 'resolved' AND DATEDIFF(DAY, dateadd(s, datediff(s, GETUTCDATE(), getdate()) +(wo.createdtime/1000), '1970-01-01 00:00:00'), GETDATE()) > 30 ORDER BY 1
NOTE: Filtered by Group name, status not closed or resolved and tickets that are older than 30 days. These can be modified as per the requirement.