Query to list request IDs with Requester and Technician Employee ID

Query to list request IDs with Requester and Technician Employee ID

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?

      Start your free trial

        Resources

            • Related Articles

            • 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 ...

            • Query to show Problem details, timespent and its associated request IDs

              ​MSSQL: SELECT "prob"."PROBLEMID" AS "Problem ID",  "prob"."TITLE" AS "Title", "statdef"."STATUSNAME" AS "Status",  "orgaaa"."FIRST_NAME" AS "Reported by", Cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs ...

            • Query for request attachment details

              Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account",  wo.WORKORDERID AS "Request ID",  wo.TITLE AS "Subject",  ti.FIRST_NAME AS "Technician",  sa.ATTACHMENTNAME "Attachment Name", sa.ATTACHMENTPATH "Attachment Path" FROM ...

            • Query to find out who created Accounts

              Execute the below queries under  Reports->New  Query Report. select org_id, org_name from accountdefinition ad where ad.org_name in('Requester Name', 'Palanivel Palras','Muhammad Nadeem Khan') Note down the Org_IDs of the above query from the result ...

            • Query to list the custom and query reports and the technician created

              Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name",               au.first_name"Created By" from customreportquery custrep LEFT JOIN customreport_details custrepdet ...