Query to show overdue tickets with delay by days

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 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 ...
                    • 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" , ...