Query to show ticket aging - PGSQL

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",  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>
        New to M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial

                      • Related Articles

                      • Query to show the last worklog added in a ticket (PGSQL)

                        Tested in build PGSQL (14300) 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", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...

                      • Query to show time elapsed in a ticket with SLA associated (PGSQL)

                        Tested in PGSQL build (14300) 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", ...

                      • Query to show status changes in a ticket_ PGSQL

                        Tested in Build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", std.STATUSNAME "Request Status", wo.TITLE "Subject", woh.OPERATION "Operation", LONGTODATE(wo.CREATEDTIME) CREATEDTIME, aau1.FIRST_NAME PERFORMEDBY, ...

                      • 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 time spent by each technician in a ticket (MSSQL & PGSQL )

                        Tested in Build PGSQL (14300) or MSSQL (14306) Navigate to Reports->New Query Report and execute this report. Under Help->About, check the database you are using and use the appropriate query. MSSQL: SELECT wo.WORKORDERID "Request ID",wo.TITLE ...

                        Related Products