Query to extract a list of technicians who haven't handled any tickets during a specific time duration (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306)
Query:
select distinct aau.user_id as "Technician User ID" ,
aau.FIRST_NAME as "Technician Name",
ac.emailid as "Technician EmailID" from aaauser aau
left join sduser sdu on aau.user_id = sdu.userid
left join aaausercontactinfo auc on aau.user_id=auc.user_id
left join aaacontactinfo ac on auc.contactinfo_id=ac.contactinfo_id
where aau.user_id in (select userid from portaltechnicians)
and aau.user_id not in (select distinct wo.requesterid from workorder wo where wo.createdtime >= datetolong('2023-04-01 00:00:00') and wo.createdtime <= datetolong('2023-04-31 00:00:00')) and sdu.STATUS = 'Active' and aau.user_id > 1
Note : Kindly change the highlighted text as per the date requirement.
New to ADSelfService Plus?
Related Articles
Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to show tickets older than 30 days ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", ...Query to show tickets created based on shift time ( PGSQL )
Tested in build PGSQL (14300) Filter mentioned by converting into minutes. PGSQL: 7.30 AM - 7.30 PM SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) AS "Created ...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 ...Query to show the time taken between created time and first technician assigned time (PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute this query. PGSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", LONGTODATE(wo.createdtime) "Created Time", LONGTODATE(wo.COMPLETEDTIME) ...