Query to show count of tickets account based (PGSQL)

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?