Query to show Contract details with assets associated (PGSQL)
Tested in PGSQL build (14300)
SELECT mcdt.customcontractid "Contract ID", mcdt.CONTRACTNAME AS "Contract Name", mcdt.customcontractid "Contract ID", contractcategory.Categoryname "Contract Type", LONGTODATE(mcdt.CREATEDDATE) AS "Created Time", ad.ORG_NAME AS "Account", rs.RESOURCENAME AS "Asset Name", product.COMPONENTNAME AS "Product", rs.SERIALNO AS "Org Serial Number" FROM MaintenanceContract mcdt LEFT JOIN Contract_Fields cadf ON mcdt.CONTRACTID=cadf.CONTRACTID LEFT JOIN VendorDefinition vdn ON mcdt.MAINTENANCEVENDOR=vdn.VENDORID LEFT JOIN SDOrganization aao ON vdn.VENDORID=aao.ORG_ID LEFT JOIN SDUser cby ON mcdt.CREATEDBY=cby.USERID LEFT JOIN AaaUser cbyaau ON cby.USERID=cbyaau.USER_ID LEFT JOIN ContractStatus cst ON mcdt.STATUSID=cst.STATUSID INNER JOIN ContractAccountmapping camp ON mcdt.contractid=camp.contractid INNER JOIN AccountDefinition ad ON camp.accountid=ad.org_id INNER JOIN ContractAccountMapping ON mcdt.CONTRACTID=ContractAccountMapping.CONTRACTID LEFT JOIN contractcategory ON mcdt.categoryid=contractcategory.categoryid
LEFT JOIN Contractdetails cd ON mcdt.contractid=cd.contractid LEFT JOIN Resources rs ON cd.resourceid=rs.resourceid
LEFT JOIN ComponentDefinition product ON rs.COMPONENTID=product.COMPONENTID LEFT JOIN ResourceLocation resLocation ON rs.RESOURCEID=resLocation.RESOURCEID
WHERE mcdt.CREATEDDATE >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2018-08-01 00:00:00') * 1000 AS BIGINT) AND mcdt.CREATEDDATE <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-08-30 00:00:00') * 1000 AS BIGINT) ORDER BY 1
New to ADSelfService Plus?
Related Articles
Contract and Service Plans details - Query Report (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill ...Query to retrieve the active and expired CPH contracts details
Tested in: 14620, 14610 and 14306 Query 1: To return the Active CPH contracts details: select ad.org_name "Account", ad.org_name "Account", sp.serviceplanname "Service Plan", ac.CONTRACTNO "Contract No", longtodate(ac.startdate) "Contract Start ...Query to get Asset contract and its details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) SELECT mcdt.Contractid "URL Contract ID", mcdt.customcontractid "Custom contractid in details page", mcdt.CONTRACTNAME "Contract Name", aao.NAME "Maintenance Vendor Name", LONGTODATE(mcdt.FROMDATE) ...Query to show technicians and associated groups (Not working )
Doesnt work in builds from PGSQL (14300) ERROR: missing FROM-clause entry for table "helpdeskcrew" SELECT AaaUser.FIRST_NAME "Technician Name", (sdo.NAME) "Associated Site", array_to_string(array_agg(distinct(qd.queuename)), ',') "Associated Group" ...Query to show PO associated assets with Invoice details (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT po.Purchaseorderid "PO ID", MAX(po.poname) "PO Name", Max(ci.ciname) "CI Name", MAX(systeminfo.SERVICETAG) "Service Tag", MAX(systeminfo.MANUFACTURER) "Manufacturer", MAX(systeminfo.MODEL) ...