Query to show Logged, Resolved and Backlog tickets based on a Region

Query to show Logged, Resolved and Backlog tickets based on a Region

SELECT rd.REGIONNAME "Region", 
count(wo.workorderid) "Logged",  
count(case when std.ispending='0' THEN 1 ELSE NULL END) "Resolved",
count(case when std.ispending='1' THEN 1 ELSE NULL END) "Backlog"
 FROM WorkOrder wo LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN Sitedefinition sd ON wo.siteid=sd.siteid LEFT JOIN Regiondefinition rd ON sd.regionid=rd.regionid WHERE (wo.ISPARENT='1')  and 
wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2021-01-01 00:00:00') * 1000 AS BIGINT)  AND  
wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2021-01-30 00:00:00') * 1000 AS BIGINT)  group by rd.REGIONNAME
          • Related Articles

          • 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 resolved by value

            PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wos.assignedtime) "Assigned Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Time", aau.FIRST_NAME AS "Requester", qd.QUEUENAME AS "Group", ...
          • Query to show tickets that are in resolved status for more than 1 hour

            MSSQL: SELECT wo.WORKORDERID AS "Request ID",ad.ORG_NAME AS "Account" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID INNER JOIN Accountsitemapping asm ON wo.siteid=asm.siteid INNER JOIN AccountDefinition ad ON ...
          • Query to show tickets first assigned today irrespective of created date

            MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request Status", ...
          • Query to show count of tickets account based

            Please go to Reports-New Query Report and execute this report. select concat(ad.org_name, '   - ', tmp.wo_count), w.workorderid "Request ID",to_timestamp((w.createdtime)/1000)::TIMESTAMP "Created time",wotodesc.FULLDESCRIPTION AS ...