Query to find the difference between technician assigned and group assigned time (PGSQL)
Tested in Build PGSQL (14300)
Please go to Reports-New Query Report and execute this query.
SELECT wo.WORKORDERID "Request id",
longtodate(min(ta.OPERATIONTIME)) "Technician assigned time",
longtodate(min(gp.OPERATIONTIME)) "Group assigned time",
TO_CHAR(((min(ta.OPERATIONTIME)-min(gp.OPERATIONTIME))/1000 || ' second')::interval, 'HH24:MI:SS') "Time to assign" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN (select woh.workorderid, woh.operationtime from workorderhistory woh left join workorderhistorydiff wohd on woh.historyid=wohd.historyid where wohd.columnname='OWNERID') ta on wo.workorderid=ta.workorderid
LEFT JOIN (select woh1.workorderid, woh1.operationtime from workorderhistory woh1 left join workorderhistorydiff wohd1 on woh1.historyid=wohd1.historyid where wohd1.columnname='queueid') gp on wo.workorderid=gp.workorderid
WHERE (wo.ISPARENT='1') and wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth> group by wo.WORKORDERID
PGSQL
New to ADSelfService Plus?
Related Articles
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 get the Request First Assigned time
Use case: First Assigned Time is the number of minutes, hours, or days between when a requester submits a request and when was a support representative assigned to the Request. It indicates how long it is in an unassigned state. Working on Build: ...Query to show technician created time (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT AaaUser.FIRST_NAME "Technician Name", aci.emailid "Email ID", LONGTODATE(AaaUser.createdtime) "Technician Created Time", SDUSER.STATUS "Status(Active/Inactive)" FROM AaaUser left ...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 show Total Onhold time ( PGSQL )
Tested in build PGSQL (14300) PGSQL: SELECT LONGTODATE(wo.CREATEDTIME) AS "Created Time", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", ...