Query to show active and archived tickets

Query to show active and archived tickets

Below is only for MSSQL. For PGSQL query, refer comments section

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'))


          • Related Articles

          • Query to show ticket aging - PGSQL

            PGSQL: select wo.workorderid as "RequestID", modedefinition.modename "Request Mode", queuedefinition.queuename "Group", cd.CategoryName as "Category", scd.name as "SubCategory", itemdefinition.name "Item",  cri.FIRST_NAME AS "Created By", rtd.name ...
          • Query to show Approved tickets per Approver

            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 "Approver Email", asd.stagename "Stage", ...
          • Query to show tickets created based on shift time_PGSQL

            Filter mentioned by converting into minutes. PGSQL: 7.30 AM - 7.30 PM SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) AS "Created Time", extract(hour from ...
          • Query to show Last added worklog of a ticket _MSSQL

            MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", wotodesc.FULLDESCRIPTION AS ...
          • Query report to show Open requests without open tasks

            PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...