# Contract Expiry and Notice Deadline SQL Query Generator

> Source: https://netray.co/tools/contract-expiry-notice-deadline-sql-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate a SQL query that finds contracts expiring soon and the last day to send a non-renewal notice, using your table and column names.

## Category

developer

## Audience

- ERP administrator
- Contracts manager
- Report developer
- Buyer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Contract table

**Key:** table

**Type:** text

**Default:** dbo.contract_header

#### Contract number column

**Key:** id_col

**Type:** text

**Default:** contract_no

#### Counterparty column

**Key:** party_col

**Type:** text

**Default:** vendor_name

#### End date column

**Key:** end_col

**Type:** text

**Default:** end_date

#### Notice period (days) column

**Key:** notice_col

**Type:** text

**Default:** notice_days

#### Auto-renew flag column

**Key:** renew_col

**Type:** text

**Default:** auto_renew

#### Annual value column

**Key:** value_col

**Type:** text

**Default:** annual_value

#### Look-ahead horizon (days)

**Key:** horizon

**Type:** number

**Default:** 120

**Blocks:**

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

**Body:** SELECT
  c.{{id_col}} AS contract_no,
  c.{{party_col}} AS counterparty,
  c.{{end_col}} AS end_date,
  c.{{notice_col}} AS notice_days,
  DATEADD(day, -c.{{notice_col}}, c.{{end_col}}) AS last_notice_date,
  DATEDIFF(day, CAST(GETDATE() AS date), DATEADD(day, -c.{{notice_col}}, c.{{end_col}})) AS days_to_notice_deadline,
  c.{{renew_col}} AS auto_renew,
  c.{{value_col}} AS annual_value
FROM {{table}} c
WHERE c.{{end_col}} IS NOT NULL
  AND c.{{end_col}} >= CAST(GETDATE() AS date)
  AND DATEADD(day, -ISNULL(c.{{notice_col}}, 0), c.{{end_col}}) <= DATEADD(day, {{horizon}}, CAST(GETDATE() AS date))
ORDER BY last_notice_date ASC, c.{{value_col}} DESC;

#### Same logic in PostgreSQL syntax

**Body:** SELECT
  c.{{id_col}} AS contract_no,
  c.{{party_col}} AS counterparty,
  c.{{end_col}} AS end_date,
  c.{{end_col}} - COALESCE(c.{{notice_col}}, 0) AS last_notice_date,
  (c.{{end_col}} - COALESCE(c.{{notice_col}}, 0)) - CURRENT_DATE AS days_to_notice_deadline,
  c.{{renew_col}} AS auto_renew,
  c.{{value_col}} AS annual_value
FROM {{table}} c
WHERE c.{{end_col}} >= CURRENT_DATE
  AND c.{{end_col}} - COALESCE(c.{{notice_col}}, 0) <= CURRENT_DATE + {{horizon}}
ORDER BY last_notice_date ASC, c.{{value_col}} DESC;

#### Summary by month of notice deadline (SQL Server)

**Body:** SELECT
  DATEFROMPARTS(YEAR(DATEADD(day, -ISNULL({{notice_col}}, 0), {{end_col}})), MONTH(DATEADD(day, -ISNULL({{notice_col}}, 0), {{end_col}})), 1) AS notice_month,
  COUNT(*) AS contracts,
  SUM({{value_col}}) AS annual_value_at_stake
FROM {{table}}
WHERE {{end_col}} >= CAST(GETDATE() AS date)
GROUP BY DATEFROMPARTS(YEAR(DATEADD(day, -ISNULL({{notice_col}}, 0), {{end_col}})), MONTH(DATEADD(day, -ISNULL({{notice_col}}, 0), {{end_col}})), 1)
ORDER BY notice_month;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use the generator

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Does this work for customer contracts too?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

contract expiry report sql, auto renewal notice deadline query, supplier contract expiration query, contract renewal alert sql server, contract end date minus notice days, erp contract expiry report, sql contracts expiring next 90 days, notice of non renewal date calculation

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json