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 time elapsed in a ticket with SLA associated_PGSQL

            PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) "Created Time", slad.slaname "SLA Associated to ...
          • 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 ...
          • 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 ...