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