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')

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