Priority change from history

Priority change from history



SELECT wo.WORKORDERID "Request ID",
       wo.TITLE "Subject",
       qd.QUEUENAME "Group",
       ti.FIRST_NAME "Technician",
       LONGTODATE(wo.CREATEDTIME) "Created Time",
       LONGTODATE(wo.DUEBYTIME) "DueBy Time",
       rtdef.NAME "Request Type",
       LONGTODATE(OPERATIONTIME) OPERATIONTIME,
       pd1.priorityname "Changed From",
       pd2.priorityname "Changed To",
       aau1.FIRST_NAME "PERFORMEDBY" FROM WorkOrder wo
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 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 WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID
LEFT JOIN AaaUser aau1 ON aau1.USER_ID = woh.OPERATIONOWNERID
LEFT JOIN PriorityDefinition pd1 ON cast(cast(wohd.prev_value AS varchar) AS int)=pd1.PRIORITYID
LEFT JOIN PriorityDefinition pd2 ON cast(cast(wohd.current_value AS varchar) AS int)=pd2.PRIORITYID
WHERE wohd.COLUMNNAME IN ('PRIORITYID')
  AND wo.CREATEDTIME >= <from_thisweek>
  AND wo.CREATEDTIME <= <to_thisweek>
ORDER BY 1,
         OPERATIONTIME DESC

 

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


                  New to ADSelfService Plus?

                    • Related Articles

                    • Change Status history

                      SELECT chdt.changeid                       "Change ID",         chdt.title                          "Title",         orgaaa.first_name                   "Requested by",         ownaaa.first_name                   "Technician",  ...
                    • Change stage history

                      SELECT chdt.changeid                       "Change ID",         chdt.title                          "Title",         orgaaa.first_name                   "Requested by",         ownaaa.first_name                   "Technician",  ...
                    • Technician change from history

                      This report is used to find who changed the Technician and how many technician handled the request.  To make any changes to a query, refer to the KB article below. ...
                    • Change approval

                      SELECT chdt.CHANGEID AS "Change ID", chdt.TITLE AS "Title", orgaaa.FIRST_NAME AS "Change Requester", qd.QUEUENAME AS "Group", ownaaa.FIRST_NAME AS "Change Owner", catadef.CATEGORYNAME AS "Category", priodef1.PRIORITYNAME AS "Priority", ...
                    • Change details

                      SELECT    chdt.changeid "Change ID",            chdt.title "Title",            Longtodate(chdt.createdtime) "Created Time",            Longtodate(chdt.scheduledstarttime) "Scheduled Start Time",  ...