Query to show due by date changes in request (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
MSSQL
SELECT wo.WORKORDERID "Request ID",
max(ti1.FIRST_NAME) "Technician" ,
max(aau.FIRST_NAME) "Requester",
max(wo.TITLE) "Subject",
max(ti.first_name) "Changed by",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (max(woh.operationtime)/1000),'1970-01-01 00:00:00') "Operation Time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((cast(cast(max(wohd.prev_value) as varchar) as bigint))/1000),'1970-01-01 00:00:00') "From Due by time",
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ((cast(cast(max(wohd.current_value) as varchar) as bigint))/1000),'1970-01-01 00:00:00') "To due by time" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti1 ON td.USERID=ti1.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
left join workorderhistory woh on wo.workorderid=woh.workorderid
left join workorderhistorydiff wohd on woh.historyid=wohd.historyid left join aaauser ti on woh.operationownerid=ti.user_id where wohd.columnname ='duebytime'
and wo.duebytime !=0 and (wo.ISPARENT='1') and wos.isoverdue ='0' and woh.operationownerid!=1 and
dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ( wo.createdtime/1000),'1970-01-01 00:00:00') >= convert(varchar,' 2019-09-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ( wo.createdtime/1000),'1970-01-01 00:00:00') <= convert(varchar,' 2019-12-30 23:59',21) group by wo.workorderid
PGSQL
Go to Reports-New Query Report and execute the query provided below.
select wo.workorderid as "ID", wo.title as "Title",longtodate(wo.createdtime) "Request Created Time",min(lastau.first_name) "First updated By",longtodate(min(woh.operationtime)) "First updated time",longtodate(wos.assignedtime) as "Technician Assigned time",longtodate(wo.RESPONDEDTIME) "First Responded Time",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 aaauser au on wos.ownerid=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 left join workorderhistory woh on woh.workorderid=wo.workorderid left join aaauser lastau on lastau.user_id=woh.operationownerid
where woh.OPERATION = 'update' and woh.historyid = (select min(historyid) from workorderhistory woh1 where woh1.workorderid=wo.workorderid and woh1.operation='update') and wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek> group by wo.workorderid,au1.first_name,wos.assignedtime,au.first_name,woq.createdtime,qd.queuename,std.statusname,lastau.first_name
order by 1
NOTE: Only field updates are considered as updated here. If a technician add a note or worklog or send reply to the request, it will not be considered as updated and it will not be shown in this query.
New to ADSelfService Plus?
Related Articles
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 ...Query to show the last worklog added in a ticket (PGSQL)
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME) "Group", max(ti.FIRST_NAME) "Assigned Technician", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...Query to show priority changes in the ticket (MSSQL)
Tested in MSSQL build (14306) This query shows which tickets were downgraded from P1. MSSQL: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", LONGTODATE(wo.DUEBYTIME) "DueBy ...Query for request attachment details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician", sa.ATTACHMENTNAME "Attachment ...Query to show technician hop count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester", ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ...