Query to show count of tickets account based

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 "Description",scd.NAME AS "Subcategory",w.title "Subject",impdef.NAME AS "Impact", rcode.NAME AS "Request Closure Code",wos.CLOSURECOMMENTS AS "Request Closure Comments" from workorder w
inner join accountsitemapping asm on w.siteid=asm.siteid
inner join (select accountid, count(workorderid) as wo_count from accountdefinition ad inner join accountsitemapping asm on asm.accountid=ad.org_id inner join workorder wo on wo.siteid=asm.siteid WHERE  ( wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2018-01-01 00:00:00') * 1000 AS BIGINT)  AND  wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2018-09-30 00:00:00') * 1000 AS BIGINT))  AND wo.ISPARENT='1' group by 1) as tmp on tmp.accountid = asm.accountid
inner join accountdefinition ad on asm.accountid=ad.org_id
LEFT JOIN WorkOrderStates wos ON w.WORKORDERID=wos.WORKORDERID
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN ImpactDefinition impdef ON wos.IMPACTID=impdef.IMPACTID
LEFT JOIN RequestClosureCode rcode ON wos.CLOSURECODEID=rcode.CLOSURECODEID
LEFT JOIN WorkOrderToDescription wotodesc ON w.WORKORDERID=wotodesc.WORKORDERID WHERE  (w.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2018-01-01 00:00:00') * 1000 AS BIGINT)  AND  w.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2018-09-30 00:00:00') * 1000 AS BIGINT))  AND w.ISPARENT='1' order by 1

POSTGRES
      • Related Articles

      • 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 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", ...
      • 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 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 ...
      • Query to show Count of survey sent and received per account

        SELECT  adef.org_name "Account",COUNT(srm.surveyid) "Total surveys sent", COUNT (sra.responseid) "Total surveys received" FROM SurveyResponseRequestMapping srrm INNER JOIN Survey_Response_Main srm ON srrm.RESPONSEID=srm.RESPONSEID LEFT JOIN ...