Query to show request details along with technician's department

Query to show request details along with technician's department

PGSQL & MSSQL:

SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group",ti.FIRST_NAME AS "Technician", dpt.DEPTNAME AS " Technician Department",  std.STATUSNAME AS "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 WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID 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 UserDepartment UD on td.USERID=ud.userid LEFT JOIN DepartmentDefinition dpt ON ud.DEPTID=dpt.DEPTID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID WHERE (wo.ISPARENT='1')

Along with requester's department:

SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester",dpt1.DEPTNAME AS " Requester Department",  wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group",ti.FIRST_NAME AS "Technician", dpt.DEPTNAME AS " Technician Department",  std.STATUSNAME AS "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 WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID 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 UserDepartment UD on td.USERID=ud.userid LEFT JOIN DepartmentDefinition dpt ON ud.DEPTID=dpt.DEPTID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN SDUser req ON wo.requesterid=req.userid LEFT JOIN AaaUser requ ON req.userid=requ.user_id LEFT JOIN UserDepartment UD1 on req.USERID=ud1.userid LEFT JOIN DepartmentDefinition dpt1 ON ud1.DEPTID=dpt1.DEPTID WHERE (wo.ISPARENT='1')



          • Related Articles

          • Query to show Request and associated task details

            PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", ti.FIRST_NAME AS "Technician", ...
          • Query to show technician hop count

            MSSQL: SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester",  ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ti1.FIRST_NAME "From technician", ...
          • Enable Account Details for basic technicians

            Account Details is an icon that is found right next to the Account drop-down in the application.  For 9.4 Builds By default Account Details tab is shown ONLY for SDAccountAdmin and SDAdmin roles.   1. Take a trimmed backup/MSSQL Backup/Server ...
          • Query to show Technician leave details

            select au.first_name "Technician", ltd.name "Leave type", longtodate(du.leavedate) "Leave date", ta.comments "Leave Comments" from techunavailability ta left join aaauser au on au.user_id=ta.technicianid left join leavetypedefinition ltd on ...
          • Query to track technician activity on tickets

            Use case: This query will help you find what are all activities that the technicians have done on requests other than the assigned ones SELECT au.first_name "Technician" ,wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE ...