Query to show tickets older than 30 days_MSSQL

Query to show tickets older than 30 days_MSSQL

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?

        Resources

            • Related Articles

            • Query to show count of aging tickets with number of days

              MSSQL: SELECT pd.PRIORITYNAME AS "Priority", count(CASE when datediff(d,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00'),GETDATE()) >0 AND datediff(d,dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + ...
            • Query to show Count of tickets based on OP Hrs_ MSSQL

              MSSQL: OP Hrs: (8.30 - 18.30) SELECT  qd.queuename "Group", "pd"."PRIORITYNAME" AS "Priority", ad.org_name "Account", count(wo.workorderid) "Total Tickets"  FROM WorkOrder wo LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN ...
            • Query to show overdue tickets with delay by days

              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 ...
            • 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 the number of days, the tickets are open

              MSSQL: 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", DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ...