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 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 ...Query to show list of softwares with last scanned date and license key (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) select systeminfo.workstationname "Workstation Name", SoftwareList.SOFTWARENAME "Software Name", LONGTODATE(SoftwareInfo.filecreatedtime) "Software Installed Date", LONGTODATE(audithistory.audittime) ...