# Installed Base Warranty Expiry SQL Query Generator

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

## Short answer

Build a T-SQL query that finds machines whose warranty ends within a chosen window, ready for service contract outreach.

## Category

developer

## Industry

Industrial Machinery

## Audience

- Service manager
- ERP analyst
- OEM aftermarket sales

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Installed base table

**Key:** ib_table

**Type:** text

**Default:** dbo.installed_base

#### Customer table

**Key:** cust_table

**Type:** text

**Default:** dbo.customers

#### Standard warranty (months)

**Key:** warranty_months

**Type:** number

**Default:** 24

#### Expiry look-ahead window (days)

**Key:** window_days

**Type:** number

**Default:** 90

**Blocks:**

#### T-SQL query

**Body:** SELECT ib.serial_no,
       ib.model,
       c.customer_name,
       ib.ship_date,
       DATEADD(MONTH, {{warranty_months}}, ib.ship_date) AS warranty_end,
       DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, {{warranty_months}}, ib.ship_date)) AS days_left
FROM {{ib_table}} AS ib
INNER JOIN {{cust_table}} AS c
        ON c.customer_id = ib.customer_id
WHERE ib.ship_date IS NOT NULL
  AND DATEADD(MONTH, {{warranty_months}}, ib.ship_date) >= CAST(GETDATE() AS date)
  AND DATEADD(MONTH, {{warranty_months}}, ib.ship_date) <= DATEADD(DAY, {{window_days}}, CAST(GETDATE() AS date))
ORDER BY warranty_end ASC;

**Notes:** Table and column names (serial_no, model, ship_date, customer_id, customer_name) are generic placeholders. Rename them to match your ERP or service database. If warranty starts at commissioning rather than shipment, swap ship_date for the commissioning date. For extended warranties, join to the contract table and use the contract end date. Test on a read replica, not on production.

## Content

**Intro:** Service revenue for an equipment builder often starts with one list: which machines come out of warranty in the next quarter. This generator writes the T-SQL for it from an installed base table and a customer table.

**Sections:**

#### How to use the generator

**Body:** Enter your installed base table name, the customer table, the standard warranty length and how far ahead you want to look. Copy the query into SQL Server Management Studio or your reporting tool and rename the columns to match your schema. Export the result to Excel for the aftermarket sales team.

**Bullets:**

- Rename generic columns before running
- Confirm what date starts the warranty
- Exclude machines already on a service contract
- Run against a read-only copy

#### What the logic does

**Body:** The query adds the warranty months to the ship date with DATEADD, compares that end date against today and today plus the window, and sorts by the earliest expiry. DATEDIFF shows the days left so the sales team can prioritise. Machines with no ship date are excluded because the end date cannot be derived.

#### Making it useful

**Body:** Add a left join to the service contract table and filter for no active contract to find true leads. Add the last service visit date to see whether the customer has an active relationship. Joining spare parts spend over the last year ranks accounts by value. The query is a starting point for a lead list, not a forecast.

#### Common mistakes

**Body:** Using ship date when the contract says commissioning date creates wrong expiry dates by weeks or months. Per-customer warranty terms, and warranty extensions after repair, need their own columns. Time zones in GETDATE can matter for expiry at midnight, so confirm whether your reports should use UTC.

**Cta:** If your service team asks these questions weekly and waits on a report writer, we can show how a read-only natural language layer over your ERP answers them directly.

**Faq:**

#### Does this work on PostgreSQL or MySQL?

**Answer:** Not as written. DATEADD, DATEDIFF and GETDATE are T-SQL. In PostgreSQL use an interval expression such as ship_date + interval '24 months' and CURRENT_DATE. MySQL uses DATE_ADD and CURDATE.

#### How do I handle different warranty terms per model?

**Answer:** Add a warranty_months column to the model or product table and replace the placeholder with that column through a join. This is more accurate than one global value.

#### Why not use the ERP standard report?

**Answer:** If your ERP has a warranty or installed base report that meets the need, use it. This query is for cases where the data lives in a custom table or you need a specific look-ahead list.

#### Can I schedule it?

**Answer:** Yes. Wrap it in a SQL Agent job or a reporting subscription that emails the result weekly to the aftermarket team. Keep access to customer data limited to those who need it.

## Search terms

warranty expiry sql query, installed base sql, machine warranty end date query, service contract renewal list sql, dateadd warranty months t-sql, oem aftermarket leads query, warranty expiring report, installed base report sql server

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