Archived and Active Request in a same report

Archived and Active Request in a same report

This Report is used to get the complete list of request both active and archived request in the same report. 


SELECT wo.WORKORDERID "Request ID",
       wo.TITLE "Subject",
       aau.FIRST_NAME "Requester",
       dpt.DEPTNAME "Department",
       ti.FIRST_NAME "Technician",
       LONGTODATE(wo.CREATEDTIME) "Created Time",
       LONGTODATE(wo.COMPLETEDTIME) "Completed Time",
       std.STATUSNAME "Request Status" 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 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 (wo.ISPARENT=1)
  AND wo.CREATEDTIME >= <from_thismonth>
  AND wo.CREATEDTIME <= <to_thismonth>
UNION
SELECT arcwo.WORKORDERID "Request ID",
       arcwo.TITLE "Subject",
       arcRequester.FIRST_NAME "Requester",
       arcwo.DEPTNAME "Department",
       arcTech.FIRST_NAME "Technician",
       LONGTODATE(arcwo.CREATEDTIME) "Created Time",
       LONGTODATE(arcwo.COMPLETEDTIME) "Completed Time",
       arcwo.STATUSNAME "Status" FROM Arc_WorkOrder arcwo
LEFT JOIN AaaUser arcRequester ON arcwo.REQUESTERID=arcRequester.USER_ID
LEFT JOIN AaaUser arcTech ON arcwo.OWNERID=arcTech.USER_ID
WHERE arcwo.CREATEDTIME >= <from_thismonth>
  AND arcwo.CREATEDTIME <= <to_thismonth> ORDER BY 1

Note : Login to ServiceDesk Plus, go to Reports tab > New Query Report > Copy the query to the query editor and run the report. 






          • Related Articles

          • Service request approvers

             This report helps to find the who all the service request approvers in the application.  SELECT aaauser.first_name "Approver Name", dpt.deptname "Department", sdo.name "Site", sd.jobtitle "Job Title" FROM srapproverdetails LEFT JOIN aaauser ON ...
          • Request recipient email

            This report is used to find the recipient email address.  Some requests are forward to others through the mail, this report helps to find the statistics how many requests are forward to others using the email address filter.  To make any changes to a ...
          • Automatically close request after successive approval reminders.

            This post describes the use of a python script to close requests after successive approval reminders using Custom Schedules. Use Case: We have an option under the self-service portal in order to send reminders mail for the approvals, what would be ...
          • Software Compliance Report

            Number of products out of compliance select swct.compliancetype "Compliance Type",count(swcd.softwareid) "Number of Products" from SWComplianceDetails swcd left join swcompliancetype swct on swcd.swcompliancetypeid=swct.swcompliancetypeid where ...
          • How to close associated incident requests of change request automatically

            This post describes the use of a python script to close change that are in a specific stage using Custom Schedules. Use Case: There may be a scenario where number of Change Requests that have got closed and the associated incident is still in open ...