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 M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • 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 ...

                    • 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 ...

                    • 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. ...

                      Related Products