Version : 13000

DB : PGSQL

SELECT mcdt.CONTRACTNAME AS "Contract Name",
Vendors.NAME as "Vendor Name",
longtodate(mcdt.FROMDATE) AS "From Date",
longtodate(mcdt.TODATE) AS "To Date",
ad.ORG_NAME AS "Account",
cst.STATUSNAME AS "Contract Status",
cadf.UDF_CHAR1 AS "Confirm Renewal With" FROM MaintenanceContract mcdt
LEFT JOIN Contract_Fields cadf ON mcdt.CONTRACTID=cadf.CONTRACTID
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
INNER JOIN SDOrganization Vendors ON mcdt.maintenancevendor=Vendors.ORG_ID
WHERE ((mcdt.CONTRACTNAME LIKE '*%'
and
extract(epoch from( to_timestamp(mcdt.todate/1000)::TIMESTAMP - now()::TIMESTAMP))/3600/24 >=1   and
extract(epoch from( to_timestamp(mcdt.todate/1000)::TIMESTAMP - now()::TIMESTAMP))/3600/24 <=60)
or
(mcdt.CONTRACTNAME not LIKE '*%'
and
extract(epoch from( to_timestamp(mcdt.todate/1000)::TIMESTAMP - now()::TIMESTAMP))/3600/24 >=1   and
extract(epoch from( to_timestamp(mcdt.todate/1000)::TIMESTAMP - now()::TIMESTAMP))/3600/24 <=30))
ORDER BY longtodate(mcdt.FROMDATE)