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?
- 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
- 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 ADSelfService Plus?
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 ...