Query to show count of tickets account based (PGSQL)
Tested in Build PGSQL (14300)
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
New to ADSelfService Plus?
Related Articles
Query to show tickets created based on shift time ( PGSQL )
Tested in build PGSQL (14300) 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 ...Query to show Count of survey sent and received per account (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 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 ...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 Logged, Resolved and Backlog tickets based on a Region (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) 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 ...Query to show users of Organization Roles for each account (MSSQL & PGSQL)
T ested in Build PGSQL (14300) or MSSQL (14306) select ad.org_name "Account Name", org.orgrolename "ORG Role Name", aau.first_name "User" from orgroles org left join roletousermapping rtu ON org.orgroleid=rtu.roleid LEFT JOIN roletouseraccountmapping ...