Custom Reports
I am trying to create a custom report that shows all tickets for the current day that were "resolved" - we set the status to resolve and then it auto closes after 24 hours. The issue is that there is no "resolveddate" field like there is "completeddate" so if I do the report based on the "completeddate" it doesnt show anything from the current day.
Here is my current query. Anyone have any suggestions?
Here is my current query. Anyone have any suggestions?
SELECT ri.FIRST_NAME 'Resolver First Name', wo.WORKORDERID 'Request ID', ti.FIRST_NAME 'Technician',aau.FIRST_NAME 'Requester',dpt.DEPTNAME 'Department',wo.TITLE 'Subject', wo.DESCRIPTION 'Description',rrs.RESOLUTION 'Resolution', dateadd(s,wo.CREATEDTIME/1000, '01-01-1970') 'Created Time', dateadd(s,wo.COMPLETEDTIME/1000, '01-01-1970') 'Completed Time' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID 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 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 RequestResolver rrr ON wo.WORKORDERID=rrr.REQUESTID LEFT JOIN RequestResolution rrs ON rrr.REQUESTID=rrs.REQUESTID Left join RequestResolver RQR ON rrr.REQUESTID=rqr.REQUESTID left join aaauser RI on rqr.technicianID=RI.USER_ID WHERE (((wo.COMPLETEDTIME >= <from_today>) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL))) AND ((wo.COMPLETEDTIME <= <to_today>) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)))) AND rrr.REQUESTID=rqr.REQUESTID and wo.COMPLETEDTIME>0 ORDER BY 1;