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 "Requester",std.STATUSNAME "Request Status",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 00:00:00') 'Created Time',dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.RESPONDEDTIME/1000),'1970-01-01 00:00:00') "Responded Date", dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.RESOLVEDTIME/1000),'1970-01-01 00:00:00') "Resolved Time",dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.COMPLETEDTIME/1000),'1970-01-01 00:00:00') "Completed Time" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN accountsitemapping asm ON siteDef.siteid=asm.siteid
LEFT JOIN accountdefinition ON accountdefinition.org_id=asm.accountid
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
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
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN workordertocharge wtc ON wtc.workorderid=wo.workorderid
LEFT JOIN chargestable ct on ct.chargeid=wtc.chargeid
LEFT JOIN ServiceDefinition serdef ON wo.SERVICEID=serdef.SERVICEID
WHERE (wo.createdtime/1000 between datediff(s,'1970-01-01 00:00','2021-03-01 00:00') and datediff(s,'1970-01-01 00:00','2021-12-31 00:00'))
UNION
SELECT arcwo.WORKORDERID "Request ID",arcwo.QUEUENAME "Group",arcTech.FIRST_NAME "Assigned Technician",arcwo.servicename "Service Category",arcwo.TITLE "Subject",arcwo.SITENAME "Site",accountdefinition.org_name "Account",arcRequester.FIRST_NAME "Requester",arcwo.STATUSNAME "Status", dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (arcwo.CREATEDTIME/1000),'1970-01-01 00:00:00') "Created Time", dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (arcwo.RESPONDEDTIME/1000),'1970-01-01 00:00:00') "Responded Date", dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (arcwo.RESOLVEDTIME/1000),'1970-01-01 00:00:00') "Resolved Time", dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (arcwo.COMPLETEDTIME/1000),'1970-01-01 00:00:00') "Completed Time" FROM Arc_WorkOrder arcwo
LEFT JOIN AaaUser arcRequester ON arcwo.REQUESTERID=arcRequester.USER_ID
LEFT JOIN AaaUser arcTech ON arcwo.OWNERID=arcTech.USER_ID
LEFT JOIN arc_workordertocharge awtc ON awtc.workorderid=arcwo.workorderid
LEFT JOIN arc_chargestable act on act.chargeid=awtc.chargeid
LEFT JOIN UserDepartment ud ON arcwo.requesterid=ud.userid
LEFT JOIN DepartmentDefinition ddef ON ud.deptid=ddef.deptid
LEFT JOIN AccountSiteMapping asm ON ddef.siteid=asm.siteid
LEFT JOIN AccountDefinition ON asm.accountid=AccountDefinition .org_id
WHERE (arcwo.createdtime/1000 between datediff(s,'1970-01-01 00:00','2021-03-01 00:00') and datediff(s,'1970-01-01 00:00','2021-12-31 00:00'))
New to ADSelfService Plus?
Related Articles
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 Approved tickets per Approver (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", std.STATUSNAME "Request Status", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", ApprovalDetails.EMAIL ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to show the last worklog added in a ticket (PGSQL)
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME) "Group", max(ti.FIRST_NAME) "Assigned Technician", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...Query to show tickets that shows total time spent of the ticket more than x hours (MSSQL)
Tested in MSSQL build (14306) Query to show tickets that shows total time spent of the ticket more than 8 hours: select ad.org_name "Account", sdo.name "Site", wo.workorderid "Request ID", au.first_name "Assigned Technician", ...