Contract expire in next 30 days

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 ADManager Plus?

                  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", ...
                    • SLA violation in next 3 days

                      When we get a list of SLA violation in next 2 days, it is important to alert the team involved in resolving the incident, to take part in SLA restoration. To make any changes to a query, refer to the KB article below. ...
                    • Close requests with pending approval for more than 10 days

                      Requirement:  Close older requests with pending approvals. UseCase:  Close all requests for which approvals have been sent but no action has been taken in more than 10 days. Please follow the below steps.  Goto Reports > New Query Report > execute ...
                    • Pending Request for more than 10 days

                      This report provides a quick view of all Pending incidents which are not closed for more than 10 days. When the incidents start backlogging faster than they can be resolved, the tendency of long incidents pending time can be exposed in this report. ...