Query to show ticket first assign/pick up time _ PGSQL

Query to show ticket first assign/pick up time _ PGSQL

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