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 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 ...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 to show purchase order full details (PGSQL)
Tested in PGSQL build (14300) SELECT po.POCUSTOMID AS "Order No.",poi.serialno "S.No", poaddfield.UDF_CHAR1 AS "Legal Entity", po.PONAME AS "PO Name", pos.STATUSNAME AS "PO Status", cy.currencyname "Currency", paao.NAME AS "Vendor Name", po.SHIPTO AS ...