Contract expire in next 30 days
MSSQL
SELECT mcdt.CONTRACTNAME "Contract Name", r.resourcename "Asset", longtodate(mcdt.todate) "Date" FROM MaintenanceContract mcdt LEFT JOIN contractdetails cd ON cd.contractid = mcdt.contractid LEFT JOIN resources r ON r.resourceid = cd.resourceid WHERE ((mcdt.todate/1000)-DATEDIFF(s, '19700101', GETDATE()))/60/60/24 >=1 AND ((mcdt.todate/1000)-DATEDIFF(s, '19700101', GETDATE()))/60/60/24 <=30 ORDER BY 1
PGSQL
SELECT mcdt.CONTRACTNAME "Contract Name", r.resourcename "Asset", longtodate(mcdt.todate) "Date" FROM MaintenanceContract mcdt LEFT JOIN contractdetails cd ON cd.contractid = mcdt.contractid LEFT JOIN resources r ON r.resourceid = cd.resourceid WHERE 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 1
New to ADSelfService Plus?
Related Articles
How to auto-create request for contract expiry.
This post describes the use of a python script to auto-create requests for contract expiry using Custom Schedules. Use case: If the contract going to expire in next 7 days, we can create a ticket with the details for the contract in ServiceDesk Plus, ...Contract Details
SELECT mcdt.Contractid "Contract ID", mcdt.CONTRACTNAME "Contract Name", mcdt.comments "Description", contractcategory.Categoryname "Contract Type", LONGTODATE(mcdt.CREATEDDATE) "Created Time", LONGTODATE(mcdt.FROMDATE) "From Date", ...Script to send multiple license expiry notification in Software License/Agreement
We can set only 1 notification for the license expiry. Using custom schedule multiple expiry notifications like 90 days before, 60 days before and 30 days before can be achieved using script. Prerequisites: 1. Python is a third party software that ...Apply Contract to All Requests raised by this account option is greyed out while creating a new contract
1. Whenever I put in Expiry date the checkbox for Apply Contract to All Requests raised by this account becomes greyed out. --> This will happen if there is another active contract for the same period. Two rules are applied while creating a contract ...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 ...