Query to list request IDs with Requester and Technician Employee ID (MSSQL)

Query to list request IDs with Requester and Technician Employee ID (MSSQL)


Tested in build MSSQL (14306)

Sample Table 


Database : MSSQL

Query

SELECT "wo"."WORKORDERID" AS "Request ID",
ti.FIRST_NAME AS "Technician",
td.employeeid "Tech Employee ID",
ti1.FIRST_NAME AS "Requester",
td1.employeeid "Req Employee ID"  FROM "WorkOrder" "wo"
LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID"
INNER JOIN "SDUser" "td" ON "wos"."OWNERID"="td"."USERID"
LEFT JOIN "AaaUser" "ti" ON "td"."USERID"="ti"."USER_ID"
LEFT JOIN "SDUser" "td1" ON "wo"."REQUESTERID"="td1"."USERID"
LEFT JOIN "AaaUser" "ti1" ON "td1"."USERID"="ti1"."USER_ID"
WHERE (wo.ISPARENT='1')

                  New to ADSelfService Plus?

                    • Related Articles

                    • Query to get the user details (both technicians and requesters) account-wise with User ID (MSSQL & PGSQL)

                      Tested in builds PGSQL (14300) or MSSQL (14306) Tested in builds : 14000, 14201, 14301 1) Query to get the First Name, Last Name, Display Name, Login User ID, Login Name, Email and Phone Number of all users in the application, account-wise, with the ...
                    • Query to show technician hop count (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) 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", ...
                    • How to Auto Share Request Upon Creation to All/Selected Technicians

                      NOTE:  This script is supported only for builds below 11.3 This post describes the use of a python script to share requests to technicians specified in a list variable in the script, which can be generated from an SQL query. This script can be ...
                    • Top Requester based on Request

                      This report is used to get the top 10 requesters count based on the request. To make any changes to a query, refer to the KB article below. https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000627781192 SELECT ...
                    • Query to track technician activity on tickets (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) 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 ...