Query to show the number of days, the tickets are open_PGSQL

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 - to_timestamp(wo.createdtime/1000)::TIMESTAMP))/3600/24) "Days open" FROM WorkOrder wo
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
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 SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (std.ispending = '1') and wo.ISPARENT='1' 
order by 8
          • Related Articles

          • Query to show the number of days, the tickets are open

            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, dateadd(s,datediff(s,GETUTCDATE() ...
          • Query to show count of aging tickets with number of days

            MSSQL: SELECT pd.PRIORITYNAME AS "Priority", count(CASE when datediff(d,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00'),GETDATE()) >0 AND datediff(d,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ...
          • 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 tickets created based on shift time_PGSQL

            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 Time", extract(hour from ...
          • Query to show overdue tickets with delay by days

            ​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 violated" FROM WorkOrder wo ...