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 "AssignedTime", wo.DueByTime as "DueByTime", woh.operationtime as "LastUpdatedTime", aaa2.first_name as "LastUpdateBy", cd.CategoryName as "CategoryName", scd.name as "SubCategoryName", (CAST(EXTRACT(EPOCH FROM now()) * 1000 AS BIGINT)-wo.createdtime)/1000/3600/24 "Aging Day(s)" from workorder "wo" left join workorderstates "wos" on wo.workorderid=wos.workorderid left join sduser "reqsu" on wo.requesterid=reqsu.userid left join aaauser "reqaaa" on reqsu.userid=reqaaa.user_id left join statusdefinition "sd" on wos.statusid=sd.statusid left join sduser "su" on wos.ownerid=su.userid left join aaauser "aaa" on su.userid=aaa.user_id left join prioritydefinition "pd" on wos.priorityid=pd.priorityid left join categorydefinition "cd" on wos.categoryid=cd.categoryid left join subcategorydefinition "scd" on wos.subcategoryid=scd.subcategoryid left join accountsitemapping "asm" on asm.siteid=wo.siteid left join (select woh1.workorderid, woh1.operationownerid, woh1,operationtime from workorderhistory "woh1" left join (select woh3.workorderid, max(woh3.operationtime) as "maxoperationtime" from workorderhistory "woh3" group by woh3.workorderid) as "woh2" on woh1.workorderid=woh2.workorderid where woh1.operationtime=woh2.maxoperationtime) as "woh" on woh.workorderid=wo.workorderid left join aaauser "aaa2" on woh.operationownerid=aaa2.user_id where wo.ISPARENT='1' and sd.statusname<>'Closed' and sd.statusname<>'Resolved' and (CAST(EXTRACT(EPOCH FROM now()) * 1000 AS BIGINT)-wo.createdtime)/1000/3600/24 > 7
New to ADSelfService Plus?
Related Articles
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 report to show Open requests without open tasks
PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...Query to show the number of days, the tickets are open (MSSQL)
Tested in Build MSSQL (14306) MSSQL: 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", DATEDIFF(day, ...Pending Request for more than 10 days
This report provides a quick view of all Pending incidents which are not closed for more than 10 days. When the incidents start backlogging faster than they can be resolved, the tendency of long incidents pending time can be exposed in this report. ...