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 STRING_AGG(wo.WORKORDERID :: TEXT, ',') AS "Aging incident ID's", COUNT(wo.WORKORDERID) AS "Aging incident count" FROM WorkOrder wo INNER JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID INNER JOIN StatusDefinition sd ON wos.STATUSID = sd.STATUSID WHERE wo.IS_CATALOG_TEMPLATE = 'false' AND sd.ISPENDING = 'false' AND TO_TIMESTAMP(wo.CREATEDTIME / 1000.0) >= ('2024-02-01 00:01' :: TIMESTAMP) AND TO_TIMESTAMP(wo.CREATEDTIME / 1000.0) <= ('2024-02-20 00:01' :: TIMESTAMP)
QUERY FOR SERVICE REQUESTS:
SELECT STRING_AGG(wo.WORKORDERID :: TEXT, ',') AS "Aging Service Request ID's", COUNT(wo.WORKORDERID) AS "Aging Service Request count" FROM WorkOrder wo INNER JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID INNER JOIN StatusDefinition sd ON wos.STATUSID = sd.STATUSID WHERE wo.IS_CATALOG_TEMPLATE = 'true' AND sd.ISPENDING = 'false' AND TO_TIMESTAMP(wo.CREATEDTIME / 1000.0) >= ('2024-02-01 00:01' :: TIMESTAMP) AND TO_TIMESTAMP(wo.CREATEDTIME / 1000.0) <= ('2024-02-20 00:01' :: TIMESTAMP)
>> The required timestamp can be selected in the highlighted area. The same highlighted area can be replaced with the following based on the requirement
To retrieve data for the current week: wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>
To retrieve data for the current month: wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth>
>> For Example:
New to ADSelfService Plus?
Related Articles
Query to get the MTTR (Mean Time To Respond & Mean Time To Resolve) reports
MTT Response Report is calculated as below, >> Response Time: Ticket Responded Time - Ticket Created Time >> MTT Response: Average total response time within a given time frame = Total Response Time within a given time frame (Divided by) Number of ...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 list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...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 * ...Query to retrieve response time in minutes
To return the ticket response time in minutes along with other request details. TESTED IN: Builds 14700 (Postgres) QUERY: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", ...