Query to find number of requests assigned to a technician. (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306)
This query is used to show how many tickets are assigned to a particular technician within a given timeframe (even if these tickets end up being re-assigned again)
PGSQL:
SELECT
aau.FIRST_NAME AS "Technician Name",
COUNT(Distinct wo.WORKORDERID) AS "Ticket Count"
FROM WORKORDER wo
LEFT JOIN WO_ASSESSMENT wa ON wo.WORKORDERID=wa.WORKORDERID
LEFT JOIN WO_TECH_INFO wti ON wa.ASSESSMENTID=wti.ASSESSMENTID
LEFT JOIN SDUSER sd ON wti.TECHNICIANID=sd.USERID
LEFT JOIN AAAUSER aau ON sd.USERID = aau.USER_ID
WHERE wa.EXECUTEDTIME >= <from_thisweek> AND wa.EXECUTEDTIME <= <to_thisweek> AND (wti.TECHNICIANID is not null) AND (wo.ISPARENT='1')
GROUP BY "Technician Name"
MSSQL:
SELECT
aau.FIRST_NAME AS "Technician Name",
COUNT(Distinct wo.WORKORDERID) AS "Ticket Count"
FROM WORKORDER wo
LEFT JOIN WO_ASSESSMENT wa ON wo.WORKORDERID=wa.WORKORDERID
LEFT JOIN WO_TECH_INFO wti ON wa.ASSESSMENTID=wti.ASSESSMENTID
LEFT JOIN SDUSER sd ON wti.TECHNICIANID=sd.USERID
LEFT JOIN AAAUSER aau ON sd.USERID = aau.USER_ID
WHERE wa.EXECUTEDTIME >= <from_thisweek> AND wa.EXECUTEDTIME <= <to_thisweek> AND (wti.TECHNICIANID is not null) AND (wo.ISPARENT='1')
GROUP BY aau.FIRST_NAME
New to ADSelfService Plus?
Related Articles
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 ...Query to show roles assigned to a technician with created date, status and department name (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query: select auser.first_name as "TECHNICIAN", max(sd.employeeid) as "Employee ID", longtodate(auser.createdtime) as "Creation Date", STRING_AGG (arole.name, ',') "Current Assigned Role", ...Query to generate the report based on the total number of requests actioned by the technicians (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) This is a comprehensive report on the total number of requests updated by a technicians. This query provides a holistic view of technician activity like updating resolution process, such as updating or ...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", ...Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) 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 ...