Hi Team,

We used below query to generate report on priority degraded tickets.

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_today>

  AND wo.CREATEDTIME <= <to_today>

ORDER BY 1,

         OPERATIONTIME DESC

 

But ,this query is not fetching alarm tickets(generated directly  from server) ..

so kindly suggest the query to generate report on priority degraded Alarm tickets