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", ti1.FIRST_NAME "From technician",
ti2.FIRST_NAME "To technician", count(wti.ASSESSMENTID) "Ticket Hop Count"
FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
left join wo_assessment woa on wo.workorderid=woa.workorderid
inner join WO_TECH_INFO wti on woa.assessmentid=wti.assessmentid
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN AaaUser ti1 ON wti.technicianid=ti1.USER_ID
LEFT JOIN AaaUser ti2 ON wti.nexttechnicianid=ti2.USER_ID
LEFT JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid
LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
WHERE (wo.ISPARENT='1') AND ti2.FIRST_NAME is NOT NULL GROUP BY wo.CREATEDTIME, wo.WORKORDERID, ti.FIRST_NAME ,qd.QUEUENAME , ti1.FIRST_NAME, ti2.FIRST_NAME, ad.ORG_NAME, aau.FIRST_NAME ORDER BY 1, 2
New to ADSelfService Plus?
Related Articles
Query to show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job ...Query to show count of tickets in each module per technician (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute this query. SELECT 'Request' "Module",aaau.first_name "Technician",count(wo.WORKORDERID) "Request count" FROM WorkOrder wo left join workorderstates wos ON ...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 KPI report based on technician (PGSQL & MSSQL)
Working on Builds: 14500 Database: MSSQL, PGSQL This report is already available under Frequently asked reports under Reports->New Query Reports page. Below query can be used for date filter and filter by technicians name modifications. SELECT ...Query to show total number of conversations, incoming and outgoing conversations (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Total Number of Conversations: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", aau.FIRST_NAME AS "Requester", ad.ORG_NAME AS "Account", std.STATUSNAME AS ...