Query to show Contract details with assets associated

Query to show Contract details with assets associated

PGSQL:

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
          • Related Articles

          • Contract and Service Plans details - Query Report

            PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill Cycle",fixedmonthlycharges "Fixed Base Charge",sp.fixedmonthlyunits ...
          • Query to show contract billing details with requests

            PGSQL: ​ select ad.org_name "Account", wo.workorderid "Request ID", wo.workorderid "Request ID", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name", sp.timeperiod "Bill Cycle", cast((sum(ct.TIMESPENT)/1000 * interval '1 second') ...
          • Query to show Problems, its associated incidents and change_ MSSQL

            SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency",  "statdef"."STATUSNAME" AS "Problem Status", "impactdef"."NAME" AS "Problem Impact", ...
          • Query to show technicians and associated groups_PGSQL

            SELECT AaaUser.FIRST_NAME "Technician Name", (sdo.NAME) "Associated Site", array_to_string(array_agg(distinct(qd.queuename)), ',') "Associated Group" FROM AaaUser  left JOIN SDUser ON AaaUser.USER_ID=SDUser.USERID  inner JOIN HelpDeskCrew ON ...
          • Query to show technicians associated accounts, sites and Support groups - MSSQL

            1.Technicians and associated Accounts/Sites: ​ select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job Title", sdo.name "Associated ...