Query to show ticket first assign/pick up time _ (PGSQL )
Tested in Build PGSQL (14300) or MSSQL (14306)
PGSQL:
SELECT wo.WORKORDERID "Request ID", wo.title "Subject", longtodate(wo.CREATEDTIME) "Request Created Time", longtodate(woh.operationtime) "First Pickup time", qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",ad.org_name "Account", pd.priorityname "Priority", rtd.name "Request Type" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID left join workorderhistory woh on wo.workorderid=woh.workorderid left join workorderhistorydiff wohd on woh.historyid=wohd.historyid INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id LEFT JOIN PriorityDefinition pd ON wos.priorityid=pd.priorityid LEFT JOIN RequestTypeDefinition rtd ON wos.requesttypeid=rtd.requesttypeid WHERE (wo.ISPARENT='1') and wohd.columnname='ownerid' and woh.historyid=(select min(workorderhistory.historyid) from workorderhistory left join workorderhistorydiff on workorderhistory.historyid=workorderhistorydiff.historyid where workorderhistory.workorderid=wo.workorderid and workorderhistorydiff.COLUMNNAME ='ownerid' ) and wo.createdtime>=DATETOLONG('2020-01-01 00:00:00') and wo.createdtime<=DATETOLONG('2020-12-31 00:00:00') AND ad.org_id in ($Account) group by wo.workorderid,wo.createdtime,woh.operationtime,Qd.QUEUENAME,ti.first_name,ad.org_name,pd.priorityname,rtd.name
Date filter can be modified in the highlighted text. If you want account specific data, choose the Account from the account drop down from the top and run the query.With time duration of unassigned time:
select wo.workorderid "ID",au1.first_name "Requested By", wo.title "Title",longtodate(wo.createdtime) "Request Created Time", longtodate(wos.assignedtime)"Technician Assigned time",au.first_name"Technician Name",longtodate(woq.createdtime) "Group Assigned time",qd.queuename "Group Name",std.STATUSNAME "Request Status", cast(((wos.assignedtime-wo.createdtime)/1000 * interval '1 second') as varchar) "Response time", LONGTODATE(wo.resolvedtime) "Resolution Time" from workorder wo left join workorderstates wos ON wo.workorderid=wos.workorderid left join wo_assessment woa ON wo.workorderid=woa.workorderid left join wo_tech_info wti ON
woa.assessmentid=wti.assessmentid left join aaauser au ON wti.nexttechnicianid=au.user_id left join aaauser au1 on wo.requesterid=au1.user_id left join workorder_queue woq on wo.workorderid=woq.workorderid left join queuedefinition qd on woq.queueid=qd.queueid left join accountsitemapping asm on wo.siteid=asm.siteid left join accountdefinition ad on ad.org_id=asm.accountid LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID where wti.technicianid is null and au.first_name is not null AND wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth> group by wo.workorderid,au.first_name,au1.first_name,wo.title,woq.createdtime,qd.queuename,wos.assignedtime,std.statusname
Assigned time taken more than 30 minutes:
select wo.workorderid "ID",au1.first_name "Requested By", wo.title "Title",longtodate(wo.createdtime) "Request Created Time", longtodate(wos.assignedtime)"Technician Assigned time", cast(((wos.assignedtime-wo.createdtime)/1000 * interval '1 second') as varchar) "Response time", au.first_name"Technician Name",longtodate(woq.createdtime) "Group Assigned time",qd.queuename "Group Name",std.STATUSNAME "Request Status", LONGTODATE(wo.resolvedtime) "Resolution Time" from workorder wo left join workorderstates wos ON wo.workorderid=wos.workorderid left join wo_assessment woa ON wo.workorderid=woa.workorderid left join wo_tech_info wti ON
woa.assessmentid=wti.assessmentid left join aaauser au ON wti.nexttechnicianid=au.user_id left join aaauser au1 on wo.requesterid=au1.user_id left join workorder_queue woq on wo.workorderid=woq.workorderid left join queuedefinition qd on woq.queueid=qd.queueid left join accountsitemapping asm on wo.siteid=asm.siteid left join accountdefinition ad on ad.org_id=asm.accountid LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID where wti.technicianid is null and au.first_name is not null AND (((wos.assignedtime)-(wo.createdtime))/1000/60) > 30 AND wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth> group by wo.workorderid,au.first_name,au1.first_name,wo.title,woq.createdtime,qd.queuename,wos.assignedtime,std.statusname
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 show unassigned time of a request and when its first assigned (MSSQL)
Tested in Build MSSQL (14306) Go to Reports-New Query Report and execute this query. MSSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", LONGTODATE(wo.createdtime) "Created Time", LONGTODATE(wo.COMPLETEDTIME) "Completed Time", ...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: ...Queries to show time stamp and time taken to first response, first tech assign, first group assign and first status set time ( MSSQL )
Tested in MSSQL build (14306) MSSQL: First responded time: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", aau.FIRST_NAME "Requester", ti.FIRST_NAME "Technician", LONGTODATE(wo.CREATEDTIME) "Created Time", LONGTODATE(wo.RESPONDEDTIME) ...Query to show first assigned and re-assigned ticket irrespective of the created date ( MSSQL)
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request ...