Query to show tickets older than 30 days ( MSSQL )

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 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 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 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 ...