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 retrieve both the live and archived requests details
Database: Pgsql Query: SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", id.NAME AS "Impact", ud.NAME AS "Urgency", rtd.NAME AS "Request Type", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved ...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 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", ...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 ...