Safety Incident Analysis SQL Query Generator

SELECT department AS department,
       COUNT(*) AS total_incidents,
       SUM(CASE WHEN is_recordable = 1 THEN 1 ELSE 0 END) AS recordables
FROM ehs_incident
WHERE site_code = 'PLANT01'
  AND incident_date >= '2026-01-01'
  AND incident_date <= '2026-09-30'
GROUP BY department
ORDER BY recordables DESC, total_incidents DESC;

SELECT EXTRACT(YEAR FROM incident_date) AS yr,
       EXTRACT(MONTH FROM incident_date) AS mth,
       incident_type AS incident_type,
       COUNT(*) AS incidents
FROM ehs_incident
WHERE site_code = 'PLANT01'
  AND incident_date >= '2026-01-01'
  AND incident_date <= '2026-09-30'
GROUP BY EXTRACT(YEAR FROM incident_date), EXTRACT(MONTH FROM incident_date), incident_type
ORDER BY yr, mth, incident_type;

WITH by_part AS (
  SELECT body_part AS body_part, COUNT(*) AS cnt
  FROM ehs_incident
  WHERE site_code = 'PLANT01'
    AND incident_date >= '2026-01-01'
    AND incident_date <= '2026-09-30'
  GROUP BY body_part
)
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;

SELECT MAX(incident_date) AS last_recordable,
       CURRENT_DATE - MAX(incident_date) AS days_since_last_recordable
FROM ehs_incident
WHERE site_code = 'PLANT01'
  AND is_recordable = 1;

-- SQL Server: DATEDIFF(day, MAX(incident_date), GETDATE())
-- Oracle: TRUNC(SYSDATE) - TRUNC(MAX(incident_date))

SELECT department AS department,
       EXTRACT(YEAR FROM incident_date) AS yr,
       EXTRACT(MONTH FROM incident_date) AS mth,
       COUNT(*) AS incidents
FROM ehs_incident
WHERE site_code = 'PLANT01'
  AND incident_date >= '2026-01-01'
  AND incident_date <= '2026-09-30'
GROUP BY department, EXTRACT(YEAR FROM incident_date), EXTRACT(MONTH FROM incident_date)
HAVING COUNT(*) >= 3
ORDER BY incidents DESC;