Query to report all requests without first reply
Tested in build PGSQL (14300)
Use case: Report all request without first response.
SELECT wo.WORKORDERID "Request ID",
wo.TITLE "Subject",
aau.FIRST_NAME "Requester",
ti.FIRST_NAME "Technician",
rtdef.NAME "Request type",
LONGTODATE(wo.CREATEDTIME) "Created Time",
LONGTODATE(wo.RESPONDEDTIME) "Responded Date",
TO_CHAR(((wo.respondedtime-wo.createdtime)/1000 || ' second')::interval, 'HH24:MI:SS') "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 StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID
WHERE (wo.ISPARENT='1')
AND RESPONDEDTIME = '0'
AND std.STATUSNAME != 'Closed'
AND std.STATUSNAME != 'Resolved'
Order by "Request ID"
Tested in build MSSQL (15220) SELECT
wo.WORKORDERID AS "Request ID",
wo.TITLE AS "Subject",
aau.FIRST_NAME AS "Requester",
ti.FIRST_NAME AS "Technician",
std.STATUSNAME AS "Request Status",
LONGTODATE(wo.CREATEDTIME) AS "Created Time",
LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date",
RIGHT('00' + CAST(((wo.RESPONDEDTIME - wo.CREATEDTIME)/1000)/3600 AS VARCHAR),2) + ':' +
RIGHT('00' + CAST(((((wo.RESPONDEDTIME - wo.CREATEDTIME)/1000)%3600)/60) AS VARCHAR),2) + ':' +
RIGHT('00' + CAST(((wo.RESPONDEDTIME - wo.CREATEDTIME)/1000)%60 AS VARCHAR),2)
AS "Created Date - Responded Date",
CAST(((DATEDIFF(SECOND, DATEADD(SECOND, wo.CREATEDTIME/1000, '1970-01-01'), GETDATE())) / 86400) AS VARCHAR)
+ ' Days ' +
CAST(((DATEDIFF(SECOND, DATEADD(SECOND, wo.CREATEDTIME/1000, '1970-01-01'), GETDATE()) % 86400) / 3600) AS VARCHAR)
+ ' Hours'
AS "Request Age"
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 StatusDefinition std ON wos.STATUSID = std.STATUSID
WHERE wo.ISPARENT = '1'
AND std.ISPENDING = 1
AND wo.RESPONDEDTIME = 0
ORDER BY wo.WORKORDERID;
New to ADSelfService Plus?
Related Articles
Query report to show Open requests without open tasks
PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...Query to generate the report based on the total number of requests actioned by the technicians (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) This is a comprehensive report on the total number of requests updated by a technicians. This query provides a holistic view of technician activity like updating resolution process, such as updating or ...Query to retrieve both the live and archived requests details
Database: Pgsql Query: SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", id.NAME AS "Impact", ud.NAME AS "Urgency", rtd.NAME AS "Request Type", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved ...How to email pending requests list to technician periodically
This post describes the use of a python script to email pending requests to each technician in a periodic interval using Custom Schedules.This showcases the use of a feature called 'Custom Schedules' that was released in the build 9300 of ServiceDesk ...Query to show parent and child requests (Linked Request) and its details.(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Please go to Reports-New Query Report and execute this query. select woparent.WORKORDERID "Parent request ID", aaauparentreq.first_name "Parent Requester", woparent.title "Parent Subject", ...