Query to show ticket aging - PGSQL

Query to show ticket aging - PGSQL


select wo.workorderid as "RequestID", modedefinition.modename "Request Mode", queuedefinition.queuename
"Group", cd.CategoryName as "Category", scd.name as "SubCategory", itemdefinition.name "Item",  cri.FIRST_NAME AS "Created By", rtd.name "Request Type", aaa.first_name as "Technician", sd.StatusName as "Request Status", LONGTODATE(wo.CreatedTime) "CreatedTime",  LONGTODATE(wo.respondedtime) "Responded Date", LONGTODATE(wo.DueByTime) "DueByTime", LONGTODATE(wo.completedtime) "Completed Time",(CAST(EXTRACT(EPOCH FROM now()) * 1000 AS BIGINT)-wo.createdtime)/1000/3600/24  "Aging Day(s)", wos.isfcr "FCR"  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 workorder_queue woq on wo.workorderid=woq.workorderid
left join queuedefinition on woq.queueid=queuedefinition.queueid LEFT JOIN SDUser crd ON wo.CREATEDBYID=crd.USERID 
 LEFT JOIN AaaUser cri ON crd.USERID=cri.USER_ID left join modedefinition
on wo.modeid=modedefinition.modeid left join statusdefinition "sd" on wos.statusid=sd.statusid LEFT JOIN Requesttypedefinition rtd ON rtd.requesttypeid=wos.requesttypeid left join itemdefinition 
on wos.itemid=itemdefinition.itemid  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 where wo.ISPARENT='1' AND wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth>

NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.

How to compare date column with auto filled date templates?
  1. Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
    • <from_thisweek> - Starting date of this week
    • <to_thisweek> - Ending date of this week
  2. Available Date Templates
    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>
          • Related Articles

          • Query to show ticket audit report

            MSSQL: Report 1: Requester, Request ID, Updated by (Technician), Updated Time : select a.first_name as "Requester",wh.workorderid as "Request ID",aa.first_name as "Updated by",longtodate(wh.OPERATIONTIME) as Updated Time from workorderhistory wh LEFT ...
          • Query to show status changes in a ticket_ PGSQL

          • Query to show the last worklog added in a ticket

            PGSQL: SELECT wo.WORKORDERID "Request ID",        max(aau.FIRST_NAME) "Requester",        max(wo.TITLE) "Subject",        max(qd.QUEUENAME) "Group",        max(ti.FIRST_NAME) "Assigned Technician",        MAX(cast((ct.TIMESPENT)/1000 * interval '1 ...
          • Request missing

            Use case: In some cases, requests are missing. We would get the ID number from acknowledgement notification but we cannot find that in the tickets. In such cases, please follow below steps to find out the details. 1. Make sure to check the Request ...
          • 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 ...