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", slad.slaname "SLA Associated to the ticket", LONGTODATE(wo.DUEBYTIME) "DueBy Time", LONGTODATE(wo.COMPLETEDTIME) "Completed Time", wos.ISOVERDUE "Overdue Status", LONGTODATE(wo.RESPONDEDTIME) "Responded Date", LONGTODATE(wo.FR_DUETIME) "Response DueBy Time", wos.IS_FR_OVERDUE AS "First Response Overdue Status", LONGTODATE(wos.LAST_TECH_UPDATE) "Last Update Time", LONGTODATE(wo.RESOLVEDTIME) "Resolved Time", TO_CHAR(((wo.TIMESPENTONREQ)/1000 || ' second')::interval, 'HH24:MI') "Time Elapsed" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 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 LEFT JOIN SLADefinition slad ON wo.slaid=slad.slaid WHERE wo.ISPARENT='1' AND ( ( std.STATUSNAME = 'Closed' ) OR ( std.STATUSNAME = 'Resolved' ) ) 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 ticket first assign/pick up time _ (PGSQL )
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject", longtodate(wo.CREATEDTIME) "Request Created Time", longtodate(woh.operationtime) "First Pickup time", qd.QUEUENAME "Group",ti.FIRST_NAME ...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", ...Time elapsed by each group in requests. PGSQL
Tested in Build PGSQL (14300) Go to Reports-New Query Report and execute the below query. SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", qd.QUEUENAME AS "Current Group", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS ...Time elapsed analysis
This Report gives a clear picture of, how long the request was handled by each technician, stayed in each group and stayed in different statuses. Capturing each action performed on the request can be helpful for the request management team to assess ...Query to show SLA assigned to tickets
Working on Builds: 14500 and above Database: PGSQL & MSSQL: SELECT slad.slaname "SLA", ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", ti.FIRST_NAME AS "Technician", rtdef.NAME AS "Request Type" ...