Query to show request ageing in days,hours,mins format- ( PGSQL )
Tested in Build PGSQL (14300)
Go to Reports-New Query Report and execute the query.
SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", qd.QUEUENAME AS "Group",concat(((CAST(EXTRACT(EPOCH FROM now()) * 1000 AS BIGINT)-wo.createdtime))/(24*3600000), ':','days' , ',' ,(((CAST(EXTRACT(EPOCH FROM now()) * 1000 AS BIGINT)-wo.createdtime))%(24*3600000))/3600000 , ':','hours' , ',' , (((CAST(EXTRACT(EPOCH FROM now()) * 1000 AS BIGINT)-wo.createdtime))%3600000)/60000 ,':','mins' ) "Days open" FROM WorkOrder wo LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE wo.ISPARENT='1' AND wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>
New to ADSelfService Plus?
Related Articles
Query to show tickets created based on shift time ( PGSQL )
Tested in build PGSQL (14300) Filter mentioned by converting into minutes. PGSQL: 7.30 AM - 7.30 PM SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) AS "Created ...Query to show the number of days, the tickets are open_PGSQL
select wo.WORKORDERID"Request ID", qd.QUEUENAME "Group", aau.FIRST_NAME "Requester", wo.TITLE "Subject", ti.FIRST_NAME "Technician", sdo.NAME "Site", LONGTODATE(wo.CREATEDTIME) "Created Time", round(extract(epoch from(now()::TIMESTAMP - ...Query to show ticket aging - PGSQL
Last tested on 14500 Database: PGSQL: select wo.workorderid as "RequestID", modedefinition.modename "Request Mode", queuedefinition.queuename "Group", cd.CategoryName as "Category", scd.name as "SubCategory", itemdefinition.name "Item", ...Query to show overdue tickets with delay by days
Working on Build's: 14500 PGSQL: SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wos.ISOVERDUE AS "Overdue Status", ad.ORG_NAME AS "Account", CAST (DATE_PART('day', now() - FROM_UNIXTIME(wo.DUEBYTIME /1000)) AS varchar)"Days since ...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 * ...