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 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 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 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) ...Query to show Last Status Changed Time and Time spent in previous status ( PGSQL )
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", ti.FIRST_NAME "Technician", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group", std.STATUSNAME "Request Status", ...