Hello,

 

I am currently running a query to generate a report for the approved incidents by the approver.  I need some help changing it so it does not show the incidents where the approver is "Not Assigned".  I also need it to only pull the approved incidents from the previous month so the report can run on the first of every month to provide proper status.  Here is the query i'm using now:

 

SELECT wo.WORKORDERID 'Request ID',aau.FIRST_NAME 'Requester',rtdef.NAME 'Request Type',wo.TITLE 'Subject',ti.FIRST_NAME 'Technician',DATE_FORMAT(FROM_UNIXTIME(wo.CREATEDTIME/1000) ,'%d-%m-%Y %k:%i')'Created Time',pd.PRIORITYNAME 'Priority',id.NAME 'Impact',aaad.EMAIL 'Approved by Email',DATE_FORMAT(FROM_UNIXTIME(apps.SENT_DATE/1000) ,'%d-%m-%Y %k:%i')'Sent On',asd.STATUSNAME 'Approval Staus',DATE_FORMAT(FROM_UNIXTIME(aaad.ACTION_DATE/1000) ,'%d-%m-%Y %k:%i') 'Approved/Rejected on',aaad.COMMENTS 'Comments'  FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 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 PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN ImpactDefinition id ON wos.IMPACTID=id.IMPACTID LEFT JOIN ApprovalStageMapping appsc on appsc.WORKORDERID=wo.WORKORDERID LEFT JOIN ApprovalStage apps on apps.APPROVAL_STAGEID=appsc.APPROVAL_STAGEID LEFT JOIN ApprovalDetails aaad on aaad.APPROVAL_STAGEID=appsc.APPROVAL_STAGEID LEFT JOIN ApprovalStatusDefinition asd on asd.STATUSID=aaad.STATUSID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (wot.THD_WOID=wot.WORKORDERID) order by 9,1

 

Thanks,
Keith