Query report on Account Details and Date of last ticket created (PGSQL)
Build no : 14304 , PGSQL
Query report columns - Account , Account creation date , Date of last ticket created
Query :
SELECT ad.ORG_NAME AS "Account", LONGTODATE(org.CREATEDTIME) AS "Account Creation Date", LONGTODATE( MAX(wo.CREATEDTIME)) AS "Date of last ticket created" FROM PortalAccounts pa LEFT JOIN AccountDefinition ad ON pa.ACCOUNTID = ad.ORG_ID LEFT JOIN WorkorderAccountMapping wam ON ad.ORG_ID = wam.ACCOUNTID LEFT JOIN Workorder wo ON wam.WORKORDERID = wo.WORKORDERID LEFT JOIN SDOrganization org ON ad.ORG_ID = org.ORG_ID GROUP BY ad.ORG_ID,ad.ORG_NAME,org.CREATEDTIME
New to ADSelfService Plus?
Related Articles
Query to show ticket aging - PGSQL
Last tested on 14500 Database: PGSQL: select wo.workorderid as "RequestID", modedefinition.modename "Request Mode", queuedefinition.queuename "Group", cd.CategoryName as "Category", scd.name as "SubCategory", itemdefinition.name "Item", ...Query to take report of all techinician details with last login time (V10.6) (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) The query is compatiable with version 10.6 and above: Query: Select au.FIRST_NAME "Technicianname" , ar.NAME "Role",al.name "Login Name",aci.emailid "EmailID",SDUSER.STATUS ...Query to show the last worklog added in a ticket (PGSQL)
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME) "Group", max(ti.FIRST_NAME) "Assigned Technician", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...