# Warranty Claims Pareto and Months-in-Service SQL Query Generator

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

## Short answer

Build SQL for a warranty claims Pareto by failure mode and a months-in-service (MIS) distribution from your own claim table.

## Category

developer

## Industry

Automotive

## Audience

- Warranty analyst
- Quality engineer
- Data analyst
- ERP developer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Claims table

**Key:** claims_table

**Type:** text

**Default:** warranty_claims

**Help:** Generic name. Replace with your real table or view.

#### Part number column

**Key:** part_col

**Type:** text

**Default:** part_no

#### Failure mode column

**Key:** mode_col

**Type:** text

**Default:** failure_mode_code

#### Claim cost column

**Key:** cost_col

**Type:** text

**Default:** claim_cost

#### In-service date column

**Key:** service_col

**Type:** text

**Default:** in_service_date

#### Claim date column

**Key:** claim_col

**Type:** text

**Default:** claim_date

#### Part number to analyse

**Key:** part_filter

**Type:** text

**Default:** AS-BRKT-210

#### Claims from

**Key:** from_date

**Type:** date

**Default:** 2025-01-01

#### Claims to

**Key:** to_date

**Type:** date

**Default:** 2026-09-30

**Blocks:**

#### 1. Pareto by failure mode with cumulative percent

**Body:** SELECT
  {{mode_col}} AS failure_mode,
  COUNT(*) AS claim_count,
  SUM({{cost_col}}) AS total_cost,
  100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS pct_of_claims,
  100.0 * SUM(COUNT(*)) OVER (ORDER BY COUNT(*) DESC, {{mode_col}}) / SUM(COUNT(*)) OVER () AS cumulative_pct
FROM {{claims_table}}
WHERE {{part_col}} = '{{part_filter}}'
  AND {{claim_col}} >= '{{from_date}}'
  AND {{claim_col}} <= '{{to_date}}'
GROUP BY {{mode_col}}
ORDER BY claim_count DESC;

#### 2. Months in service distribution (SQL Server syntax)

**Body:** SELECT
  DATEDIFF(month, {{service_col}}, {{claim_col}}) AS months_in_service,
  COUNT(*) AS claim_count,
  SUM({{cost_col}}) AS total_cost
FROM {{claims_table}}
WHERE {{part_col}} = '{{part_filter}}'
  AND {{claim_col}} >= '{{from_date}}'
  AND {{claim_col}} <= '{{to_date}}'
GROUP BY DATEDIFF(month, {{service_col}}, {{claim_col}})
ORDER BY months_in_service;

#### 3. Months in service (PostgreSQL syntax)

**Body:** SELECT
  (EXTRACT(YEAR FROM age({{claim_col}}, {{service_col}})) * 12
   + EXTRACT(MONTH FROM age({{claim_col}}, {{service_col}})))::int AS months_in_service,
  COUNT(*) AS claim_count,
  SUM({{cost_col}}) AS total_cost
FROM {{claims_table}}
WHERE {{part_col}} = '{{part_filter}}'
  AND {{claim_col}} >= '{{from_date}}'
  AND {{claim_col}} <= '{{to_date}}'
GROUP BY 1
ORDER BY 1;

**Notes:** Table and column names are generic. Map them to your claims data and check date handling for your database. Pareto percentages count claims, not vehicles, so combine with in-service volume to get claims per thousand vehicles.

## Content

**Intro:** Two views drive most warranty investigations: which failure modes account for most of the claims, and how soon after sale they appear. This generator writes the SQL for both against your own claims table, with the cumulative percentage that a Pareto chart needs.

**Sections:**

#### How to use the generator

**Body:** Enter the name of your claims table or view and the columns that hold part number, failure mode, cost, in-service date and claim date. Choose the part and date window, then copy the queries. The first query returns the Pareto data. The second returns the months-in-service distribution, in SQL Server or PostgreSQL syntax. Run them on a read replica or a reporting copy, not on your transactional system during production hours.

#### Reading the Pareto

**Body:** Sort order and the cumulative percentage show where effort pays back. If three failure modes account for around 80 percent of claims, those are the first 8D or CAPA candidates. Sort by cost as well as count, because a rare failure with a high claim value can outweigh a frequent cheap one. Keep failure modes coded consistently, otherwise the Pareto fragments into near-duplicates and hides the real leader.

**Bullets:**

- Cumulative percent uses a window function
- Run once by count and once by cost
- Clean up failure mode codes before charting

#### Months in service

**Body:** A months-in-service distribution shows when parts fail relative to when the vehicle entered service. A spike in the first months points to manufacturing or assembly problems and early-life failures. A rising tail points to wear-out or durability issues. Compare production month cohorts to see whether a process change coincided with the shift. This is the basic structure behind warranty analysis guidance such as the AIAG CQI-14 guideline.

#### Common mistakes

**Body:** The main mistake is analysing claims without exposure. Ten claims on a part that sold one thousand vehicles is different from ten claims on a part that sold one hundred thousand. Also check for claims with missing in-service dates, which will produce nulls or negative months, and exclude or review them. Finally, remember currency and goodwill claims can distort cost totals.

**Cta:** If warranty data sits in one system and production lots in your ERP, Netray can discuss a private assistant that links them and shows the exact query it ran.

**Faq:**

#### Why does the Pareto use a window function?

**Answer:** A window function lets one query compute each failure mode's count, its share of all claims and the running cumulative percentage in a single pass. That gives the data needed to draw the cumulative line on a Pareto chart.

#### Will this work on Oracle or MySQL?

**Answer:** The Pareto query uses standard window functions available in modern Oracle, MySQL 8 and others. The months-in-service queries are shown for SQL Server and PostgreSQL, so adjust the date difference function for other databases.

#### How do I get claims per thousand vehicles?

**Answer:** Divide the claim count by the number of vehicles or parts in service for the same period and multiply by one thousand. You need exposure data from sales or production, which these queries do not include.

#### Can I run this directly on my ERP database?

**Answer:** Prefer a reporting copy or read-only view. The queries only read data, but long scans can slow a transactional system. Test on a small date range and have a database administrator review before scheduling.

## Search terms

warranty claims pareto sql, months in service query, warranty analysis SQL, failure mode pareto query, MIS warranty distribution, automotive warranty data query, cumulative percent window function sql, warranty cost by part number

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