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 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 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 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 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 get total worklog time spent on a request. (PGSQL)
Tested in build PGSQL (14300) Scenario: Request1 worklog1-Total time taken to resolve=1 hr 20 mins. worklog2- Total time taken to resolve=1hr 10mins. While generating a report the total time taken to resolve for request1 should be 2 hrs 30mins. ...