Developer ToolsFree generatorPDF, Excel, Word export

Contract Expiry and Notice Deadline SQL Query Generator

A contract that auto-renews silently is one of the most avoidable costs in procurement. This generator builds a query that lists contracts near their end date and computes the last day to send notice, so renewals are decisions rather than accidents.

Fill in

Contract table

Contract number column

Counterparty column

End date column

Notice period (days) column

Auto-renew flag column

Annual value column

Look-ahead horizon (days)

Generated code

Download:

Contracts expiring or needing notice within the horizon (SQL Server syntax)

SELECT
  c.contract_no AS contract_no,
  c.vendor_name AS counterparty,
  c.end_date AS end_date,
  c.notice_days AS notice_days,
  DATEADD(day, -c.notice_days, c.end_date) AS last_notice_date,
  DATEDIFF(day, CAST(GETDATE() AS date), DATEADD(day, -c.notice_days, c.end_date)) AS days_to_notice_deadline,
  c.auto_renew AS auto_renew,
  c.annual_value AS annual_value
FROM dbo.contract_header c
WHERE c.end_date IS NOT NULL
  AND c.end_date >= CAST(GETDATE() AS date)
  AND DATEADD(day, -ISNULL(c.notice_days, 0), c.end_date) <= DATEADD(day, 120, CAST(GETDATE() AS date))
ORDER BY last_notice_date ASC, c.annual_value DESC;

Same logic in PostgreSQL syntax

SELECT
  c.contract_no AS contract_no,
  c.vendor_name AS counterparty,
  c.end_date AS end_date,
  c.end_date - COALESCE(c.notice_days, 0) AS last_notice_date,
  (c.end_date - COALESCE(c.notice_days, 0)) - CURRENT_DATE AS days_to_notice_deadline,
  c.auto_renew AS auto_renew,
  c.annual_value AS annual_value
FROM dbo.contract_header c
WHERE c.end_date >= CURRENT_DATE
  AND c.end_date - COALESCE(c.notice_days, 0) <= CURRENT_DATE + 120
ORDER BY last_notice_date ASC, c.annual_value DESC;

Summary by month of notice deadline (SQL Server)

SELECT
  DATEFROMPARTS(YEAR(DATEADD(day, -ISNULL(notice_days, 0), end_date)), MONTH(DATEADD(day, -ISNULL(notice_days, 0), end_date)), 1) AS notice_month,
  COUNT(*) AS contracts,
  SUM(annual_value) AS annual_value_at_stake
FROM dbo.contract_header
WHERE end_date >= CAST(GETDATE() AS date)
GROUP BY DATEFROMPARTS(YEAR(DATEADD(day, -ISNULL(notice_days, 0), end_date)), MONTH(DATEADD(day, -ISNULL(notice_days, 0), end_date)), 1)
ORDER BY notice_month;

Table and column names are generic placeholders. Replace them with your own contract master, ERP blanket order or purchasing agreement table. Verify date arithmetic for your database, and check whether a notice period is in calendar or business days under the contract. Run read-only first.

Contract deadline alerts from your ERP

An optional discussion of building a read-only contract expiry and notice alert across your systems.

Optional. The tool and every export work without this. No spam.

How to use the generator

Fill in your own table and column names. If your ERP stores agreements across several tables, such as a header and a terms table, join them in the FROM clause after pasting. The first query returns each contract with its last notice date and the days left. The third query groups by month so you can see how much annual spend has a notice deadline in each month.

Run against a read-only copy or reporting database
Schedule it weekly and email the output
Add owner and category columns for routing
Keep the horizon above your longest notice period

The calculation behind it

The last notice date is the end date minus the notice period. For a contract ending on 31 December with a 90 day notice requirement, notice must reach the other side by about 2 October, which is earlier than most people expect. The query also treats a missing notice period as zero, which you should review because blank values usually mean the data was never captured, not that no notice is needed.

Reading the output

Sort by days to notice deadline and focus on negative values: those contracts have passed their notice date and may already have renewed. Contracts with an auto renew flag and a high annual value deserve attention first. For evergreen contracts with no end date, use a separate review cycle, since this query excludes null end dates.

Common mistakes

Storing notice periods as free text, mixing calendar and business days, ignoring amendments that moved the end date, and forgetting to define who must deliver the notice and how. A date column on its own is not a contract management system. Keep the signed agreement as the source of truth and use the query to prompt a human check.

Frequently Asked Questions

Does this work for customer contracts too?

Yes. Point the table and counterparty columns at customer agreements. The logic is the same: end date minus the notice period gives the last day to give or receive notice, depending on who must act.

Why is the notice date earlier than I expected?

Because notice periods count back from the end date. A 90 day notice on a contract ending 31 December must be given around 2 October. Many contracts also require notice to be received, not just sent.

What if my notice period is in months?

Convert months to days with an approximation for reporting, or change the DATEADD call to use month units. Always confirm the exact date under the contract wording, since month lengths vary.

Is this safe to run on my ERP database?

It is a SELECT only, but run it on a reporting copy or off-peak, and have your DBA review table names and indexes first. Do not change the query to write data without a review process.

If you want these dates pulled from several systems and shown with the clause text for review, Netray can build a read-only contract deadline view over your ERP and document store.

Talk to an Expert Explore More Free Tools

Related Tools

Compliance & Contracts

More Developer Tools tools

See all

Back to All Tools