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 ...Script to Auto close Request when the status is Resolved for a particular number of days
For version 11 and above: -> In version 11, we only have the option to set the request Auto-close for a max of 10 days -> The below script will help you to extend the auto-closure time Execution steps: 1. Go to Reports--> New Query Report and run the ...Unable to start the application due to Pgsql privilege issue
ISSUE: Application does not start via both services and command prompt. ERROR TRACE: Serverout: [16:26:36:443]|[05-10-2023]|[com.adventnet.db.adapter.postgres.PostgresDBAdapter]|[SEVERE]|[27]: Exception occurred while obtaining mode of the database| ...