Query to show First response actioned by (MSSQL)
Tested in build MSSQL (14306)
SELECT wo.WORKORDERID "Request ID",
wo.TITLE "Subject",
aau.FIRST_NAME "Requester",
ti.FIRST_NAME "Technician",
LONGTODATE(wo.CREATEDTIME) "Created Time",
LONGTODATE(wo.RESPONDEDTIME) "Responded Date",
sdu2.firstname "Technician who added FR",
convert(varchar(10), ((wo.RESPONDEDTIME)-(wo.createdtime))/1000/3600)+':'+convert(varchar(10), (((wo.RESPONDEDTIME)-(wo.createdtime))/1000)%3600/60)+':'+convert(varchar(10),((((wo.RESPONDEDTIME)-(wo.createdtime)))/1000%60)) "Created Date - Responded Date" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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 WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID
LEFT JOIN SDUSER sdu2 ON woh.operationownerid=sdu2.userid
WHERE (wo.ISPARENT='1') AND WOH.Operation='REPLY' AND woh.historyid = (select min(historyid) from workorderhistory woh1 where woh1.workorderid=wo.workorderid and woh1.operation='REPLY')
AND RESPONDEDTIME !='0'
AND wo.CREATEDTIME >= <from_thisweek>
AND wo.CREATEDTIME <= <to_thisweek>
New to ADSelfService Plus?
Related Articles
Query to show Last added worklog of a ticket ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", ...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 ...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 last comments added in Projects task_MSSQL
SELECT taskdet.TASKID AS "Task ID", taskdet.TITLE AS "Title", taskowner.FIRST_NAME AS "Owner", taskcreatedby.FIRST_NAME AS "Created By", taskstatus.STATUSNAME AS "Task Status", taskdesc.DESCRIPTION AS "Description", ...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 ...