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 Time", LONGTODATE(wo.DUEBYTIME) AS "Due By Time", LONGTODATE(wo.COMPLETEDTIME) AS "Completed TIme", LONGTODATE(wos.LAST_TECH_UPDATE) AS "Last Update Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date", LONGTODATE(wo.FR_DUETIME) AS "Response DueBy Time", concat(floor(wo.TIMESPENTONREQ/(1000*60*60*24)),':',floor(Mod(wo.TIMESPENTONREQ, 24*60*60*1000)/(60*60*1000)),':',floor(mod(Mod(wo.TIMESPENTONREQ, 24*60*60*1000),(60*60*1000))/(60*1000))) AS "Time Elapsed", wo.TITLE AS "Subject", sd.STATUSNAME AS "Request Status", asd.STATUSNAME AS "Approval Status", req.FIRST_NAME AS "Technician", tech.FIRST_NAME AS "Requester", qd.QUEUENAME AS "Group", cd.CATEGORYNAME AS "Category", md.MODENAME AS "Request Mode", wos.IS_FR_OVERDUE AS "First Response Over Due Status", wos.ISOVERDUE AS "Overdue Status", scd.NAME AS "Subcategory", idn.NAME AS "Item", creater.FIRST_NAME AS "Created By", wos.ISFCR AS "FCR", wos.REOPENED AS "ReOpened", STRING_AGG(re.RESOURCENAME,',') AS "Asset Name", wos.CLOSURECOMMENTS AS "Request Closure Comments", sdo.NAME AS "Site" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID = cd.CATEGORYID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID = scd.SUBCATEGORYID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID = woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID = qd.QUEUEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID = pd.PRIORITYID LEFT JOIN ImpactDefinition id ON wos.IMPACTID = id.IMPACTID LEFT JOIN UrgencyDefinition ud ON wos.URGENCYID = ud.URGENCYID LEFT JOIN RequestTypeDefinition rtd ON wos.REQUESTTYPEID = rtd.REQUESTTYPEID LEFT JOIN StatusDefinition sd ON wos.STATUSID = sd.STATUSID LEFT JOIN AAAUser req ON wo.REQUESTERID = req.USER_ID LEFT JOIN AAAUser tech ON wos.OWNERID = tech.USER_ID LEFT JOIN ModeDefinition md ON wo.MODEID = md.MODEID LEFT JOIN ApprovalStatusDefinition asd ON wos.APPR_STATUSID = asd.STATUSID LEFT JOIN ITEMDEFINITION idn ON wos.ITEMID = idn.ITEMID LEFT JOIN AAAUser creater ON wo.CREATEDBYID = creater.USER_ID LEFT JOIN WorkOrderToAsset woa ON wo.WORKORDERID = woa.WORKORDERID LEFT JOIN Resources re ON woa.ASSETID = re.RESOURCEID LEFT JOIN SDOrganization sdo ON wo.SITEID = sdo.ORG_ID WHERE (wo.ISPARENT = '1') GROUP BY wo.WORKORDERID, pd.PRIORITYNAME, id.NAME, ud.NAME, rtd.NAME, wo.CREATEDTIME, wo.RESOLVEDTIME, wo.DUEBYTIME, wo.TIMESPENTONREQ, wo.TITLE, sd.STATUSNAME, asd.STATUSNAME, req.FIRST_NAME, tech.FIRST_NAME, qd.QUEUENAME, cd.CATEGORYNAME, md.MODENAME, wos.IS_FR_OVERDUE, wo.FR_DUETIME, wos.ISOVERDUE, wo.COMPLETEDTIME, scd.NAME, idn.NAME, creater.FIRST_NAME, wo.RESPONDEDTIME, wos.ISFCR, wos.REOPENED, wos.CLOSURECOMMENTS, wos.LAST_TECH_UPDATE, sdo.NAME UNION SELECT arcwo.WORKORDERID AS "Request ID", arcwo.PRIORITYNAME AS "Priority", arcwo.IMPACTNAME AS "Impact", arcwo.URGENCYNAME AS "Urgency", arcwo.REQUESTTYPE AS "Request Type", LONGTODATE(arcwo.CREATEDTIME) AS "Created Time", LONGTODATE(arcwo.RESOLVEDTIME) AS "Resolved Time", LONGTODATE(arcwo.DUEBYTIME) AS "Due By Time", LONGTODATE(arcwo.LAST_TECH_UPDATE) AS "Last Update Time", LONGTODATE(arcwo.RESPONDEDTIME) AS "Responded Date", LONGTODATE(arcwo.COMPLETEDTIME) AS "Completed TIme", LONGTODATE(arcwo.FR_DUETIME) AS "Response DueBy Time", concat(floor(arcwo.TIMESPENTONREQ/(1000*60*60*24)),':',floor(Mod(arcwo.TIMESPENTONREQ, 24*60*60*1000)/(60*60*1000)),':',floor(mod(Mod(arcwo.TIMESPENTONREQ, 24*60*60*1000),(60*60*1000))/(60*1000))) AS "Time Elapsed", arcwo.TITLE AS "Subject", arcwo.STATUSNAME AS "Request Status", arcwo.APPR_STATUSNAME AS "Approval Status", req.FIRST_NAME AS "Technician", tech.FIRST_NAME AS "Requester", arcwo.QUEUENAME AS "Group", arcwo.CATEGORYNAME AS "Category", arcwo.MODENAME AS "Request Mode", arcwo.IS_FR_OVERDUE AS "First Response Over Due Status", arcwo.ISOVERDUE AS "Overdue Status", arcwo.SUBCATEGORYNAME AS "Subcategory", arcwo.ITEMNAME AS "Item", creater.FIRST_NAME AS "Created By", arcwo.ISFCR AS "FCR", arcwo.REOPENED AS "ReOpened", STRING_AGG(re.RESOURCENAME,',') AS "Asset Name", arcwo.CLOSURECOMMENTS AS "Request Closure Comments", arcwo.SITENAME AS "Site" FROM Arc_WorkOrder arcwo LEFT JOIN AAAUser req ON arcwo.REQUESTERID = req.USER_ID LEFT JOIN AAAUser tech ON arcwo.OWNERID = tech.USER_ID LEFT JOIN AAAUser creater ON arcwo.CREATEDBYID = creater.USER_ID LEFT JOIN Arc_WorkOrderToAsset awoa ON arcwo.WORKORDERID = awoa.WORKORDERID LEFT JOIN Resources re ON awoa.ASSETID = re.RESOURCEID GROUP BY arcwo.WORKORDERID, arcwo.PRIORITYNAME, arcwo.IMPACTNAME, arcwo.URGENCYNAME, arcwo.REQUESTTYPE, arcwo.CREATEDTIME, arcwo.RESOLVEDTIME, arcwo.DUEBYTIME, arcwo.TIMESPENTONREQ, arcwo.TITLE, arcwo.STATUSNAME, arcwo.APPR_STATUSNAME, req.FIRST_NAME, tech.FIRST_NAME, arcwo.QUEUENAME, arcwo.CATEGORYNAME, arcwo.MODENAME, arcwo.IS_FR_OVERDUE, arcwo.FR_DUETIME, arcwo.ISOVERDUE, arcwo.COMPLETEDTIME, arcwo.SUBCATEGORYNAME, arcwo.ITEMNAME, creater.FIRST_NAME, arcwo.RESPONDEDTIME, arcwo.ISFCR, arcwo.REOPENED, arcwo.CLOSURECOMMENTS, arcwo.LAST_TECH_UPDATE, arcwo.SITENAME
Output:
The query retrieves the below mentioned columns.
Query with Account Filter:
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 Time", LONGTODATE(wo.DUEBYTIME) AS "Due By Time", LONGTODATE(wo.COMPLETEDTIME) AS "Completed TIme", LONGTODATE(wos.LAST_TECH_UPDATE) AS "Last Update Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date", LONGTODATE(wo.FR_DUETIME) AS "Response DueBy Time", concat( floor(wo.TIMESPENTONREQ /(1000 * 60 * 60 * 24)), ':', floor( Mod(wo.TIMESPENTONREQ, 24 * 60 * 60 * 1000) /(60 * 60 * 1000) ), ':', floor( mod( Mod(wo.TIMESPENTONREQ, 24 * 60 * 60 * 1000), (60 * 60 * 1000) ) /(60 * 1000) ) ) AS "Time Elapsed", wo.TITLE AS "Subject", sd.STATUSNAME AS "Request Status", asd.STATUSNAME AS "Approval Status", req.FIRST_NAME AS "Technician", tech.FIRST_NAME AS "Requester", qd.QUEUENAME AS "Group", cd.CATEGORYNAME AS "Category", md.MODENAME AS "Request Mode", wos.IS_FR_OVERDUE AS "First Response Over Due Status", wos.ISOVERDUE AS "Overdue Status", scd.NAME AS "Subcategory", idn.NAME AS "Item", creater.FIRST_NAME AS "Created By", wos.ISFCR AS "FCR", wos.REOPENED AS "ReOpened", STRING_AGG(re.RESOURCENAME, ',') AS "Asset Name", wos.CLOSURECOMMENTS AS "Request Closure Comments", sdo.NAME AS "Site" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID = cd.CATEGORYID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID = scd.SUBCATEGORYID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID = woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID = qd.QUEUEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID = pd.PRIORITYID LEFT JOIN ImpactDefinition id ON wos.IMPACTID = id.IMPACTID LEFT JOIN UrgencyDefinition ud ON wos.URGENCYID = ud.URGENCYID LEFT JOIN RequestTypeDefinition rtd ON wos.REQUESTTYPEID = rtd.REQUESTTYPEID LEFT JOIN StatusDefinition sd ON wos.STATUSID = sd.STATUSID LEFT JOIN AAAUser req ON wo.REQUESTERID = req.USER_ID LEFT JOIN AAAUser tech ON wos.OWNERID = tech.USER_ID LEFT JOIN ModeDefinition md ON wo.MODEID = md.MODEID LEFT JOIN ApprovalStatusDefinition asd ON wos.APPR_STATUSID = asd.STATUSID LEFT JOIN ITEMDEFINITION idn ON wos.ITEMID = idn.ITEMID LEFT JOIN AAAUser creater ON wo.CREATEDBYID = creater.USER_ID LEFT JOIN WorkOrderToAsset woa ON wo.WORKORDERID = woa.WORKORDERID LEFT JOIN Resources re ON woa.ASSETID = re.RESOURCEID LEFT JOIN SDOrganization sdo ON wo.SITEID = sdo.ORG_ID LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID = wam.WORKORDERID LEFT JOIN AccountDefinition ad ON wam.ACCOUNTID = ad.ORG_ID WHERE (wo.ISPARENT = '1') AND ad.ORG_NAME LIKE '%DHLeCS%' GROUP BY wo.WORKORDERID, pd.PRIORITYNAME, id.NAME, ud.NAME, rtd.NAME, wo.CREATEDTIME, wo.RESOLVEDTIME, wo.DUEBYTIME, wo.TIMESPENTONREQ, wo.TITLE, sd.STATUSNAME, asd.STATUSNAME, req.FIRST_NAME, tech.FIRST_NAME, qd.QUEUENAME, cd.CATEGORYNAME, md.MODENAME, wos.IS_FR_OVERDUE, wo.FR_DUETIME, wos.ISOVERDUE, wo.COMPLETEDTIME, scd.NAME, idn.NAME, creater.FIRST_NAME, wo.RESPONDEDTIME, wos.ISFCR, wos.REOPENED, wos.CLOSURECOMMENTS, wos.LAST_TECH_UPDATE, sdo.NAME UNION SELECT arcwo.WORKORDERID AS "Request ID", arcwo.PRIORITYNAME AS "Priority", arcwo.IMPACTNAME AS "Impact", arcwo.URGENCYNAME AS "Urgency", arcwo.REQUESTTYPE AS "Request Type", LONGTODATE(arcwo.CREATEDTIME) AS "Created Time", LONGTODATE(arcwo.RESOLVEDTIME) AS "Resolved Time", LONGTODATE(arcwo.DUEBYTIME) AS "Due By Time", LONGTODATE(arcwo.LAST_TECH_UPDATE) AS "Last Update Time", LONGTODATE(arcwo.RESPONDEDTIME) AS "Responded Date", LONGTODATE(arcwo.COMPLETEDTIME) AS "Completed TIme", LONGTODATE(arcwo.FR_DUETIME) AS "Response DueBy Time", concat( floor(arcwo.TIMESPENTONREQ /(1000 * 60 * 60 * 24)), ':', floor( Mod(arcwo.TIMESPENTONREQ, 24 * 60 * 60 * 1000) /(60 * 60 * 1000) ), ':', floor( mod( Mod(arcwo.TIMESPENTONREQ, 24 * 60 * 60 * 1000), (60 * 60 * 1000) ) /(60 * 1000) ) ) AS "Time Elapsed", arcwo.TITLE AS "Subject", arcwo.STATUSNAME AS "Request Status", arcwo.APPR_STATUSNAME AS "Approval Status", req.FIRST_NAME AS "Technician", tech.FIRST_NAME AS "Requester", arcwo.QUEUENAME AS "Group", arcwo.CATEGORYNAME AS "Category", arcwo.MODENAME AS "Request Mode", arcwo.IS_FR_OVERDUE AS "First Response Over Due Status", arcwo.ISOVERDUE AS "Overdue Status", arcwo.SUBCATEGORYNAME AS "Subcategory", arcwo.ITEMNAME AS "Item", creater.FIRST_NAME AS "Created By", arcwo.ISFCR AS "FCR", arcwo.REOPENED AS "ReOpened", STRING_AGG(re.RESOURCENAME, ',') AS "Asset Name", arcwo.CLOSURECOMMENTS AS "Request Closure Comments", arcwo.SITENAME AS "Site" FROM Arc_WorkOrder arcwo LEFT JOIN AAAUser req ON arcwo.REQUESTERID = req.USER_ID LEFT JOIN AAAUser tech ON arcwo.OWNERID = tech.USER_ID LEFT JOIN AAAUser creater ON arcwo.CREATEDBYID = creater.USER_ID LEFT JOIN Arc_WorkOrderToAsset awoa ON arcwo.WORKORDERID = awoa.WORKORDERID LEFT JOIN Resources re ON awoa.ASSETID = re.RESOURCEID LEFT JOIN Arc_WorkOrderAccountMapping arwam ON arcwo.WORKORDERID = arwam.WORKORDERID LEFT JOIN AccountDefinition ad ON arwam.ACCOUNTID = ad.ORG_ID WHERE ad.ORG_NAME LIKE '%DHLeCS%' GROUP BY arcwo.WORKORDERID, arcwo.PRIORITYNAME, arcwo.IMPACTNAME, arcwo.URGENCYNAME, arcwo.REQUESTTYPE, arcwo.CREATEDTIME, arcwo.RESOLVEDTIME, arcwo.DUEBYTIME, arcwo.TIMESPENTONREQ, arcwo.TITLE, arcwo.STATUSNAME, arcwo.APPR_STATUSNAME, req.FIRST_NAME, tech.FIRST_NAME, arcwo.QUEUENAME, arcwo.CATEGORYNAME, arcwo.MODENAME, arcwo.IS_FR_OVERDUE, arcwo.FR_DUETIME, arcwo.ISOVERDUE, arcwo.COMPLETEDTIME, arcwo.SUBCATEGORYNAME, arcwo.ITEMNAME, creater.FIRST_NAME, arcwo.RESPONDEDTIME, arcwo.ISFCR, arcwo.REOPENED, arcwo.CLOSURECOMMENTS, arcwo.LAST_TECH_UPDATE, arcwo.SITENAME
Replace the required account name in the highlighted
New to ADSelfService Plus?
Related Articles
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 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 Change Details
Tested in: 14504, 14610 QUERY: SELECT chdt.CHANGEID AS "Change ID", cmDef.FIRST_NAME AS "Change Manager", ownaaa.FIRST_NAME AS "Change Owner", approvaldef.STATUSNAME AS "Approval Status", qd.QUEUENAME AS "Group", statusDef.STATUSDISPLAYNAME AS ...Query to retrieve the active and expired CPH contracts details
Tested in: 14620, 14610 and 14306 Query 1: To return the Active CPH contracts details: select ad.org_name "Account", ad.org_name "Account", sp.serviceplanname "Service Plan", ac.CONTRACTNO "Contract No", longtodate(ac.startdate) "Contract Start ...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 ...