Query to retrieve the requests details

Query to retrieve the requests details

Tested in: 14610, 14301


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", TO_CHAR((wo.TIMESPENTONREQ / 1000 || ' second') :: interval, 'HH24:MI') AS "Time taken for resolution", pd.PRIORITYNAME AS "Priority", wo.IS_CATALOG_TEMPLATE AS "Is Service Request", mdd.MODENAME AS "Mode", std.STATUSNAME AS "Status", qd.QUEUENAME AS "Group", TO_CHAR((wo.ONHOLDTIME / 1000 || ' second') :: interval, 'HH24:MI') AS "Hold time", CASE WHEN COUNT(wgi.ASSESSMENTID) > 0 THEN COUNT(wgi.ASSESSMENTID) -1 ELSE 0 END AS "No. of Hops" FROM WorkOrder wo LEFT JOIN ModeDefinition mdd ON wo.MODEID = mdd.MODEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID = std.STATUSID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID = pd.PRIORITYID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID = woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID = qd.QUEUEID LEFT JOIN WO_Assessment wa ON wo.WORKORDERID = wa.WORKORDERID LEFT JOIN WO_Group_Info wgi ON wa.ASSESSMENTID = wgi.ASSESSMENTID WHERE (wo.ISPARENT = '1') 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) GROUP BY wo.WORKORDERID, wo.CREATEDTIME, wo.RESPONDEDTIME, wo.RESOLVEDTIME, wo.TIMESPENTONREQ, pd.PRIORITYNAME, wo.IS_CATALOG_TEMPLATE, mdd.MODENAME, std.STATUSNAME, qd.QUEUENAME, wo.ONHOLDTIME

>> 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> 

>> The query returns the below output:

>> Time taken for resolution: ticket resolved time - created time.
>> Hold Time: Time when the tickets were in "On Hold" status.
>> No. of Hops: Number of times a request was interchanged between support groups.

                    New to ADSelfService Plus?