Query to get Assigned Tech and Escaleted Technician info with requester contact number.

Query to get Assigned Tech and Escaleted Technician info with requester contact number.

Version : 11026
DB : MSSQL


OUTPUT:




SELECT wo.TITLE AS "Subject",
wo.WORKORDERID AS "Request ID",
AaaContactInfo.LANDLINE "Customer Phone",
AaaContactInfo.MOBILE "Customer Mobile",
prev.first_name AS  "Assigend by",
longtodate(wos.ASSIGNEDTIME) AS "Assigned Time",
ti.FIRST_NAME AS "Support Rep",
au.FIRST_NAME "Escalation Technician" ,
longtodate(woslaesc.ESCALATIONTIME) "Escalated Date",
longtodate(wo.CREATEDTIME) AS "Created Time",
longtodate(wo.RESOLVEDTIME) AS "Resolved Time" FROM WorkOrder wo
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 workorderhistory woh ON wo.workorderid=woh.workorderid
LEFT JOIN workorderhistorydiff wohd ON woh.historyid=wohd.historyid
LEFT JOIN SDUser sdReq ON wo.REQUESTERID=sdReq.USERID
LEFT JOIN AaaUser auReq ON sdReq.USERID=auReq.USER_ID
LEFT JOIN AaaUserContactInfo ON auReq.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT JOIN aaauser prev ON cast(cast(woh. OPERATIONOWNERID AS varchar) AS int)=prev.user_id
LEFT JOIN requestslaescalations woslaesc ON wo.WORKORDERID=woslaesc.itemid
Left JOIN slaescalation slaesc1 on  woslaesc.escalationid = slaesc1.escalationid
LEFT JOIN escalatetomediator escmed1 on slaesc1.escalatetoid = escmed1.escalatetoid
left JOIN escalateton escon1 on escmed1.escalatetoid = escon1.escalatetoid
LEFT join SdUser on  escon1.userid=SdUser.userid
LEFT join aaaUser au on  SdUser.userid=au.user_id WHERE (wo.ISPARENT='1') and wohd.columnname='ASSIGNEDTIME'


              New to ADManager Plus?

                New to ADSelfService Plus?