# Fiber Optic Test Results SQL Schema and Pass/Fail Query Generator

> Source: https://netray.co/tools/fiber-optic-test-results-sql-schema-and-pass-fail-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Creates a table for insertion loss and return loss results and queries that find failures and yield by wavelength.

## Category

developer

## Industry

Fiber Optics

## Audience

- Test engineer
- Manufacturing engineer
- ERP developer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Table name

**Key:** table

**Type:** text

**Default:** fiber_test_result

#### Max insertion loss (dB)

**Key:** il_limit

**Type:** number

**Default:** 0.30

**Help:** Per mated pair or per assembly, from your specification

#### Min return loss (dB)

**Key:** rl_limit

**Type:** number

**Default:** 50

**Help:** For example 50 dB for UPC singlemode, 60 dB for APC, as specified

#### Wavelength (nm)

**Key:** wavelength

**Type:** select

**Options:**

- 850
- 1300
- 1310
- 1550

**Default:** 1310

#### Report from date

**Key:** from_date

**Type:** date

**Default:** 2026-10-01

#### Report to date

**Key:** to_date

**Type:** date

**Default:** 2026-10-31

**Blocks:**

#### 1. Create the table

**Body:** CREATE TABLE {{table}} (
  test_id        INTEGER PRIMARY KEY,
  serial_no      VARCHAR(40) NOT NULL,
  work_order     VARCHAR(30),
  part_no        VARCHAR(40),
  fiber_no       INTEGER,
  wavelength_nm  INTEGER NOT NULL,
  insertion_loss_db DECIMAL(6,3),
  return_loss_db    DECIMAL(6,2),
  length_m       DECIMAL(10,2),
  operator_id    VARCHAR(20),
  meter_serial   VARCHAR(40),
  tested_at      TIMESTAMP NOT NULL
);

CREATE INDEX ix_{{table}}_serial ON {{table}} (serial_no);
CREATE INDEX ix_{{table}}_date ON {{table}} (tested_at);

#### 2. Sample insert

**Body:** INSERT INTO {{table}} (test_id, serial_no, work_order, part_no, fiber_no, wavelength_nm, insertion_loss_db, return_loss_db, length_m, operator_id, meter_serial, tested_at)
VALUES (1, 'SN-000101', 'WO-5521', 'JMP-LC-SM-2M', 1, {{wavelength}}, 0.12, 54.5, 2.0, 'OP17', 'PM-3302', '{{from_date}} 09:15:00');

#### 3. Units that fail the limits

**Body:** SELECT serial_no, fiber_no, wavelength_nm, insertion_loss_db, return_loss_db, tested_at
FROM {{table}}
WHERE wavelength_nm = {{wavelength}}
  AND tested_at >= '{{from_date}}' AND tested_at < '{{to_date}}'
  AND (insertion_loss_db > {{il_limit}} OR return_loss_db < {{rl_limit}})
ORDER BY tested_at;

#### 4. First-pass yield by day

**Body:** SELECT CAST(tested_at AS DATE) AS test_day,
       COUNT(*) AS tests,
       SUM(CASE WHEN insertion_loss_db <= {{il_limit}} AND return_loss_db >= {{rl_limit}} THEN 1 ELSE 0 END) AS passed,
       100.0 * SUM(CASE WHEN insertion_loss_db <= {{il_limit}} AND return_loss_db >= {{rl_limit}} THEN 1 ELSE 0 END) / COUNT(*) AS pass_pct
FROM {{table}}
WHERE wavelength_nm = {{wavelength}}
  AND tested_at >= '{{from_date}}' AND tested_at < '{{to_date}}'
GROUP BY CAST(tested_at AS DATE)
ORDER BY test_day;

#### 5. Loss statistics by operator

**Body:** SELECT operator_id,
       COUNT(*) AS tests,
       AVG(insertion_loss_db) AS avg_il_db,
       MAX(insertion_loss_db) AS max_il_db,
       MIN(return_loss_db) AS min_rl_db
FROM {{table}}
WHERE wavelength_nm = {{wavelength}}
  AND tested_at >= '{{from_date}}' AND tested_at < '{{to_date}}'
GROUP BY operator_id
ORDER BY avg_il_db DESC;

**Notes:** Generic ANSI-style SQL. Adjust types, CAST and date syntax for your database (SQL Server, PostgreSQL, Oracle, MySQL). Column names are examples, not a vendor schema. Use limits from your own specification.

## Content

**Intro:** Test results kept on the test PC are hard to trend or find when a customer asks about a serial number. This generator writes a simple table for insertion loss and return loss results and the queries you need for failures, yield and operator comparison.

**Sections:**

#### How to use the generator

**Body:** Fill in a table name, your insertion loss and return loss limits, the test wavelength and the report dates. Copy the SQL into your database tool, create the table, and load results from your test software export. The queries then show failing serial numbers, daily first-pass yield and loss statistics by operator.

#### Why store results by serial number

**Body:** Keeping every measurement keyed by serial number, work order and wavelength lets you answer customer questions in seconds and compare results over time. It also allows traceability to the meter serial, which matters when a calibration issue is found later. The schema is intentionally narrow so you can load CSV exports from most test software.

**Bullets:**

- Index on serial number for customer lookups.
- Index on date for yield reports.
- Store the meter serial to trace calibration issues.

#### Things to verify

**Body:** Check the insertion loss limit per standard or customer: for example, TIA-568 allows up to 0.75 dB per mated connector pair in a cabling channel, but a factory-tested jumper often has a much tighter limit. Return loss is higher is better, so the fail test uses a less-than comparison. Decide how to treat multiple retests of the same serial: keep all records and query the latest, or add a retest flag.

**Cta:** Netray can connect this kind of test table to your ERP serial and work order data so quality and production see the same record.

**Faq:**

#### Why is return loss compared with less than?

**Answer:** Return loss is a positive dB value where higher is better, since it means less light is reflected. A unit fails when its value is below the minimum, so the failing condition uses a less-than comparison.

#### Can I use this in SQL Server or PostgreSQL?

**Answer:** Yes, with small changes. Replace TIMESTAMP with DATETIME2 in SQL Server, and adjust the CAST to DATE where needed. The structure and queries are standard SQL.

#### How do I handle retests?

**Answer:** Store every test with its time, then query the latest row per serial number using a window function or a subquery on the maximum tested_at. This keeps the history and gives an accurate final result.

#### Should I store OTDR traces here too?

**Answer:** Store the trace files in a file store and keep the file path or ID in this table, along with event summary values if needed. Trace data is large and better kept outside relational tables.

## Search terms

fiber optic test data database, insertion loss sql table, fiber test results sql query, optical test yield query, store OTDR test results database, manufacturing test data schema, pass fail query insertion loss, return loss limit query

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