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", sdo.NAME as "Site", LONGTODATE(Wo.createdtime) "Created Time", LONGTODATE(wos.last_tech_update) "Last Updated Time", ti1.FIRST_NAME "From technician",
ti2.FIRST_NAME "To technician", cast((wti.timespent/60) as varchar(20)) +':'+cast((wti.timespent % 60) as varchar(20)) "Time Spent by each technician", DATEDIFF(DAY, dateadd(s, datediff(s, GETUTCDATE(), getdate()) +(wo.createdtime/1000), '1970-01-01 00:00:00'), GETDATE()) "Days since created" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN AaaUser aau ON wo.REQUESTERID=aau.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN accountsitemapping asm on asm.siteid=sitedef.siteid LEFT JOIN accountdefinition adef on adef.org_id=asm.accountid LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 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 wo_assessment woa on wo.workorderid=woa.workorderid
inner join WO_TECH_INFO wti on woa.assessmentid=wti.assessmentid
LEFT JOIN AaaUser ti1 ON wti.technicianid=ti1.USER_ID
LEFT JOIN AaaUser ti2 ON wti.nexttechnicianid=ti2.USER_ID
WHERE wo.ISPARENT='1' and QUEUENAME= 'Customer Operations' AND std.STATUSNAME!= 'closed' and std.STATUSNAME!= 'resolved' AND DATEDIFF(DAY, dateadd(s, datediff(s, GETUTCDATE(), getdate()) +(wo.createdtime/1000), '1970-01-01 00:00:00'), GETDATE()) > 30 ORDER BY 1
NOTE: Filtered by Group name, status not closed or resolved and tickets that are older than 30 days. These can be modified as per the requirement.
New to ADSelfService Plus?
Related Articles
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 ...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 tickets created during and outside Operational Hrs ( MSSQL )
Tested in MSSQL build (14306) NON OP Hrs: SELECT "wo"."WORKORDERID" AS "Request ID", LONGTODATE(wo.createdtime) "Created Time", "wo"."TITLE" AS "Title", queuedefinition.queuename "Group", au.first_name "Technician", "pd"."PRIORITYNAME" AS "Priority", ...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", ...Query to show active and archived tickets (MSSQL)
Tested in build MSSQL (14306) SELECT wo.WORKORDERID "Request ID",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",serdef.NAME "Service Category",wo.TITLE "Subject",sdo.NAME "Site",accountdefinition.org_name "Account",aau.FIRST_NAME ...