# Safety Incident Analysis SQL Query Generator

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

## Short answer

Generate ready-to-run SQL for common EHS incident analyses from your own table and column names.

## Category

ehs-workforce

## Industry

Manufacturing

## Audience

- Safety manager
- EHS analyst
- BI developer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Incident table

**Key:** tbl

**Type:** text

**Default:** ehs_incident

**Help:** Your table or view name. These queries use generic names, not a specific EHS product.

#### Incident date column

**Key:** dt

**Type:** text

**Default:** incident_date

#### Department column

**Key:** dept

**Type:** text

**Default:** department

#### Incident type column

**Key:** typ

**Type:** text

**Default:** incident_type

#### Body part column

**Key:** body

**Type:** text

**Default:** body_part

#### Recordable flag column

**Key:** rec

**Type:** text

**Default:** is_recordable

**Help:** Assumed to hold 1 for recordable and 0 otherwise.

#### Site code

**Key:** site

**Type:** text

**Default:** PLANT01

#### Site column

**Key:** site_col

**Type:** text

**Default:** site_code

#### From date

**Key:** from

**Type:** date

**Default:** 2026-01-01

#### To date

**Key:** to

**Type:** date

**Default:** 2026-09-30

**Blocks:**

#### Incidents and recordables by department

**Body:** SELECT {{dept}} AS department,
       COUNT(*) AS total_incidents,
       SUM(CASE WHEN {{rec}} = 1 THEN 1 ELSE 0 END) AS recordables
FROM {{tbl}}
WHERE {{site_col}} = '{{site}}'
  AND {{dt}} >= '{{from}}'
  AND {{dt}} <= '{{to}}'
GROUP BY {{dept}}
ORDER BY recordables DESC, total_incidents DESC;

#### Monthly trend by incident type

**Body:** SELECT EXTRACT(YEAR FROM {{dt}}) AS yr,
       EXTRACT(MONTH FROM {{dt}}) AS mth,
       {{typ}} AS incident_type,
       COUNT(*) AS incidents
FROM {{tbl}}
WHERE {{site_col}} = '{{site}}'
  AND {{dt}} >= '{{from}}'
  AND {{dt}} <= '{{to}}'
GROUP BY EXTRACT(YEAR FROM {{dt}}), EXTRACT(MONTH FROM {{dt}}), {{typ}}
ORDER BY yr, mth, incident_type;

#### Body part pareto with cumulative share

**Body:** WITH by_part AS (
  SELECT {{body}} AS body_part, COUNT(*) AS cnt
  FROM {{tbl}}
  WHERE {{site_col}} = '{{site}}'
    AND {{dt}} >= '{{from}}'
    AND {{dt}} <= '{{to}}'
  GROUP BY {{body}}
)
SELECT body_part,
       cnt,
       ROUND(100.0 * cnt / SUM(cnt) OVER (), 1) AS pct,
       ROUND(100.0 * SUM(cnt) OVER (ORDER BY cnt DESC) / SUM(cnt) OVER (), 1) AS cumulative_pct
FROM by_part
ORDER BY cnt DESC;

#### Last recordable and days since (PostgreSQL syntax)

**Body:** SELECT MAX({{dt}}) AS last_recordable,
       CURRENT_DATE - MAX({{dt}}) AS days_since_last_recordable
FROM {{tbl}}
WHERE {{site_col}} = '{{site}}'
  AND {{rec}} = 1;

-- SQL Server: DATEDIFF(day, MAX({{dt}}), GETDATE())
-- Oracle: TRUNC(SYSDATE) - TRUNC(MAX({{dt}}))

#### Repeat locations (departments with 3 or more incidents in a month)

**Body:** SELECT {{dept}} AS department,
       EXTRACT(YEAR FROM {{dt}}) AS yr,
       EXTRACT(MONTH FROM {{dt}}) AS mth,
       COUNT(*) AS incidents
FROM {{tbl}}
WHERE {{site_col}} = '{{site}}'
  AND {{dt}} >= '{{from}}'
  AND {{dt}} <= '{{to}}'
GROUP BY {{dept}}, EXTRACT(YEAR FROM {{dt}}), EXTRACT(MONTH FROM {{dt}})
HAVING COUNT(*) >= 3
ORDER BY incidents DESC;

**Notes:** Table and column names are placeholders: replace them with those in your own EHS database or ERP safety module. EXTRACT and window functions work in PostgreSQL, Oracle and recent MySQL; in SQL Server use YEAR() and MONTH(). Test on a copy of the data and keep personal information out of reports.

## Content

**Intro:** Most EHS systems and ERP safety modules hold incidents in a table that anyone with read access can query. This generator writes five commonly needed analyses from your own table and column names, so you can paste them into a SQL client, a BI tool or a report definition.

**Sections:**

#### How to use it

**Body:** Fill in the table and column names exactly as they appear in your database, set the site code and date range, and copy each query. Run them with a read-only account. If your data model splits incidents and injuries into separate tables, join them on the incident key before using the pareto query. The queries use generic names so they work with in-house tables, spreadsheets loaded into a database, or exports from a safety app.

#### What each query answers

**Body:** The department query shows where incidents cluster. The monthly trend shows whether a campaign is working. The body part pareto, with cumulative percent, points to which injury types make up most of the cases, which is useful when choosing between glove, guarding or ergonomic projects. The repeat location query surfaces departments that need a closer look. The days since query feeds a plant scoreboard.

**Bullets:**

- Use leading indicators next to lagging ones where you can
- Count near misses in a separate query so they are not mixed with injuries
- Do not publish small cells that could identify an individual
- Agree on recordable definitions with your regulator rules first

#### Dialect and data quality notes

**Body:** Date arithmetic differs by database, so the days since query shows three variants. Free-text body part fields need cleaning before a pareto is meaningful, a pick list is far better. Check that late-entered incidents are dated by the event, not the report date. Compare the output against the OSHA 300 log or your national equivalent as a control total.

**Cta:** If your incident data lives in an ERP, a separate EHS tool and spreadsheets, we can help bring it into one governed dataset with read-only access and a clear audit trail.

**Faq:**

#### Will these queries run against my ERP?

**Answer:** They will once you replace the placeholder names with the real table and column names. Some ERPs have no EHS table at all, so you may need your own table or a view that standardizes the fields used here.

#### Can I calculate TRIR with this?

**Answer:** These queries produce the counts. TRIR also needs hours worked, which usually sit in HR or payroll. Join or enter that value and apply the rate formula, 200,000 times recordables divided by hours worked.

#### Why use a view rather than the raw table?

**Answer:** A view lets you present stable column names, filter out test records and restrict personal data. It also isolates your reports when the vendor changes the table design during upgrades.

#### Is it safe to expose injury data in dashboards?

**Answer:** Only with care. Health information about identifiable people is sensitive and may be regulated. Aggregate by department and month, hide small counts and restrict access by role.

## Search terms

safety incident SQL query, incident report database query, EHS data analysis SQL, days since last recordable SQL, injury pareto by body part query, safety KPI dashboard SQL, recordable incidents by department query

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