Query to show overdue tickets with delay by days
Working on Build's: 14500
PGSQL:
SELECT ad.ORG_NAME AS "Account",
wo.WORKORDERID AS "Request ID",
wos.ISOVERDUE AS "Overdue Status",
ad.ORG_NAME AS "Account",
CAST (DATE_PART('day', now() - FROM_UNIXTIME(wo.DUEBYTIME /1000)) AS varchar)"Days since violated" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
WHERE (wo.ISPARENT='1' and wos.ISOVERDUE = 'true')
ORDER BY 1
MSSQL:
SELECT wo.workorderid "Request ID",
wotodesc.FULLDESCRIPTION AS "Description",
wos.ISOVERDUE AS "Overdue Status",
longtodate(wo.DUEBYTIME) AS "DueBy Time",
DATEDIFF (day, DATEADD(s, wo.DUEBYTIME/1000, '01-01-1970 00:00:00'), getDate()) "Time Delay in Days",
dpt.DEPTNAME AS "Department",
ti.FIRST_NAME AS "Technician" FROM WorkOrder wo
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
WHERE (wo.ISPARENT='1' and wos.ISOVERDUE = 'true')
GROUP BY wo.workorderid,wotodesc.FULLDESCRIPTION,wos.ISOVERDUE,wo.DUEBYTIME,dpt.DEPTNAME,ti.FIRST_NAME
Sample Table
New to ADSelfService Plus?
Related Articles
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" ...Query to show tickets older than 30 days ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", ...Query to show the number of days, the tickets are open_PGSQL
select wo.WORKORDERID"Request ID", qd.QUEUENAME "Group", aau.FIRST_NAME "Requester", wo.TITLE "Subject", ti.FIRST_NAME "Technician", sdo.NAME "Site", LONGTODATE(wo.CREATEDTIME) "Created Time", round(extract(epoch from(now()::TIMESTAMP - ...Query to get complete SLA details. (PGSQL)
Tested in build PGSQL (14300) To get the complete list of SLA configured in the application. PGSQL: SELECT sdo.name "Site Name" , sla.slaname "SLA", MAX(sla.duebydays) "SLA Days", MAX(sla.duebyhours) "SLA Hours", MAX(sla.duebyminutes) "SLA Minutes" , ...Query to show response, resolution SLA violated count and its percentage ( PGSQL & MSSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT pd.PRIORITYNAME AS "Priority" , count(wo.WORKORDERID) "Total Requests", count(case when ( std.ispending='0') THEN 1 ELSE NULL END) "Closed", count(case when (wos.IS_FR_OVERDUE='1') THEN 1 ELSE ...