Query to retrieve the aging days details of requests
Tested in: 14610 (Postgres)
Query:
SELECT wo.WORKORDERID AS "Request ID", req.FIRST_NAME AS "Requester", LONGTODATE(wo.CREATEDTIME) AS "Created Date", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date", (FLOOR(wo.TIMESPENTONREQ / (1000 * 60 * 60 * 24))::INT) AS "Ageing Day", (CASE WHEN wo.IS_CATALOG_TEMPLATE = 'true' THEN 'Service Request' ELSE 'Incident Request' END ) AS "Request Type", qd.QUEUENAME AS "Group", pd.PRIORITYNAME AS "Priority" FROM WorkOrder wo LEFT JOIN AaaUser req ON wo.REQUESTERID =req.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID =wos.WORKORDERID LEFT JOIN WorkOrder_Queue wq ON wo.WORKORDERID=wq.WORKORDERID LEFT JOIN QueueDefinition qd ON wq.QUEUEID = qd.QUEUEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID = pd.PRIORITYID WHERE wo.WORKORDERID NOT IN (SELECT wo1.WORKORDERID FROM WorkOrder wo1 LEFT JOIN WorkOrderStates wos1 ON wo1.WORKORDERID = wos1.WORKORDERID LEFT JOIN StatusDefinition std1 ON wos1.STATUSID = std1.STATUSID LEFT JOIN AaaUser tech ON wos1.OWNERID = tech.USER_ID WHERE tech.FIRST_NAME = 'Mr Robot' AND std1.ISPENDING ='false')
Result:
New to ADSelfService Plus?
Related Articles
Query to retrieve the requests that are created and completed within the given time frame
Tested in: 14610 & 14301 The following queries will return the IDs and counts of requests created within a given time frame and completed (Resolved, Closed, Cancelled) within the same time frame. QUERY FOR INCIDENT REQUESTS: SELECT ...Query to retrieve the requests details
Tested in: 14610, 14301 QUERY: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date and Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date and Time", ...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 ...Requests that are open for more than 7 days (PGSQL)
Tested in Build PGSQL (14300) PGSQL: select wo.workorderid as "RequestID", wo.Title as "Title", reqaaa.First_Name as "Requester", aaa.first_name as "Technician", sd.StatusName as "Status", wo.CreatedTime as "CreatedTime", wos.AssignedTime as ...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", ...