Query to show priority changes in the ticket (MSSQL)

Query to show priority changes in the ticket (MSSQL)

Tested in MSSQL build (14306)

This query shows which tickets were downgraded from P1.

MSSQL:

SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", LONGTODATE(wo.DUEBYTIME) "DueBy Time", LONGTODATE(wo.CREATEDTIME) "Created Time", sdo.NAME AS "Site", pd.PRIORITYNAME AS "Priority", qd.QUEUENAME AS "Group", ad.ORG_NAME AS "Account", cd.CATEGORYNAME AS "Category", wos.ISOVERDUE AS "Overdue Status", wos.IS_FR_OVERDUE AS "First Response Overdue Status", aau1.FIRST_NAME PERFORMEDBY,
LONGTODATE(OPERATIONTIME) OPERATIONTIME,
pd1.PRIORITYNAME "Changed From",
pd2.PRIORITYNAME "Changed To" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID 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 CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID  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 pd1.PRIORITYNAME='P1'

Query works on latest builds too


                New to ADManager Plus?

                  New to ADSelfService Plus?