NTSB investigations, WI

580 cases where the phase of flight was Enroute.

Cases, newest first

Query with SQL
duckdb -csv -c "WITH phase_names(main, name) AS (VALUES (100, 'Prior to flight'), (150, 'Standing'), (200, 'Pushback/towing'), (250, 'Taxi'), (300, 'Takeoff'), (350, 'Initial climb'), (400, 'Enroute'), (450, 'Maneuvering'), (500, 'Approach'), (550, 'Landing'), (600, 'Emergency descent'), (650, 'Uncontrolled descent'), (700, 'Post-impact'), (750, 'After landing'), (800, 'Other'), (990, 'Unknown')),
    crosswalk(code, main, name) AS (VALUES (500, 150, 'Standing'), (501, 150, 'Standing - pre-flight'), (502, 150, 'Standing - starting engine(s)'), (503, 150, 'Standing - engine(s) operating'), (504, 150, 'Standing - engine(s) not operating'), (505, 150, 'Standing - idling rotors'), (510, 250, 'Taxi'), (511, 200, 'Taxi - pushback/tow'), (512, 250, 'Taxi - to takeoff'), (513, 250, 'Taxi - from landing'), (514, 250, 'Taxi - aerial'), (520, 300, 'Takeoff'), (521, 300, 'Takeoff - roll/run'), (522, 350, 'Takeoff - initial climb'), (523, 300, 'Takeoff - aborted'), (530, 400, 'Climb'), (531, 400, 'Climb - to cruise'), (540, 400, 'Cruise'), (541, 400, 'Cruise - normal'), (542, 400, 'Maneuvering - holding (IFR)'), (550, 400, 'Descent'), (551, 400, 'Descent - normal'), (552, 600, 'Descent - emergency'), (553, 650, 'Descent - uncontrolled'), (560, 500, 'Approach'), (561, 500, 'Approach - VFR pattern - downwind'), (562, 500, 'Approach - VFR pattern - base turn'), (563, 500, 'Approach - VFR pattern - base leg/base to final'), (564, 500, 'Approach - VFR pattern - final approach'), (565, 500, 'Go-around (VFR)'), (566, 500, 'Approach - IAF to FAF/outer marker (IFR)'), (567, 500, 'Approach - FAF/outer marker to threshold (IFR)'), (568, 500, 'Circling (IFR)'), (569, 500, 'Missed approach (IFR)'), (570, 550, 'Landing'), (571, 550, 'Landing - flare/touchdown'), (572, 550, 'Landing - roll'), (573, 550, 'Landing - aborted'), (574, 550, 'Emergency landing'), (575, 550, 'Emergency landing after takeoff'), (576, 600, 'Emergency descent/landing'), (580, 450, 'Maneuvering'), (581, 450, 'Maneuvering - aerial application'), (582, 450, 'Maneuvering - turn to reverse direction'), (583, 450, 'Maneuvering - turn to landing area (emergency)'), (590, 450, 'Hover'), (591, 450, 'Hover - in ground effect'), (592, 450, 'Hover - out of ground effect'), (600, 800, 'Other'), (610, 990, 'Unknown')),
    current_codes AS (
      SELECT e.case_mkey, TRY_CAST(e.\"tier1Number\" AS INTEGER) AS code,
        row_number() OVER (PARTITION BY e.case_mkey ORDER BY coalesce(e.\"isDefiningEvent\", false) DESC, e.position) AS n
      FROM 'https://openairdocs.org/data/latest/ntsb_events.parquet' e
      WHERE e.aircrafts_position = 1 AND e.\"tier1Number\" GLOB '[1-9][0-9]*'
        AND (e.\"isDefiningEvent\" OR e.case_mkey IN (SELECT \"mKey\" FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' WHERE \"isPre1982\"))
    ),
    phases AS (
      SELECT case_mkey, 'current' AS scheme, code, CASE WHEN code >= 990 THEN 990 ELSE code // 50 * 50 END AS main
      FROM current_codes WHERE n = 1
      UNION ALL
      SELECT o.case_mkey, 'older', o.\"phaseOfFlightCode\", w.main
      FROM 'https://openairdocs.org/data/latest/ntsb_occurrences.parquet' o JOIN crosswalk w ON w.code = o.\"phaseOfFlightCode\"
      WHERE o.aircrafts_position = 1 AND o.\"occurrenceNumber\" = 1 AND o.case_mkey NOT IN (SELECT case_mkey FROM current_codes)
    ),
    case_phases AS (
      SELECT p.*, c.\"eventDate\", c.\"highestInjuryLevel\" = 'Fatal' AS fatal, a.aircraft_model_code
      FROM phases p JOIN 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c ON c.\"mKey\" = p.case_mkey
      LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = p.case_mkey AND a.position = 1
      WHERE c.\"eventDate\" >= DATE '1962-01-01' AND p.main IN (SELECT main FROM phase_names)
    )
  SELECT c.\"ntsbNumber\", c.\"eventDate\", c.\"eventType\", c.\"eventCity\", c.\"eventStateOrRegion\", a.n_number, a.\"aircraftMake\", a.\"aircraftModel\", c.\"highestInjuryLevel\", c.\"totalFatal\", c.\"completionStatus\", c.report_url
  FROM 'https://openairdocs.org/data/latest/ntsb_case_detail.parquet' c LEFT JOIN 'https://openairdocs.org/data/latest/ntsb_aircrafts.parquet' a ON a.case_mkey = c.\"mKey\" AND a.position = 1
  WHERE c.\"eventStateOrRegion\" = 'WI' AND c.\"eventCountry\" = 'USA'
    AND c.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 400)
  ORDER BY c.\"eventDate\" DESC, c.\"ntsbNumber\" DESC"

Prints the whole list as CSV. It reads the release’s Parquet files over HTTPS, so there’s nothing to download first. Install DuckDB to run it.

NTSB CHI77FA039Accident · 3 fatalClosedNa Rockwell 112 · Hudson, WIApr 10, 1977 · N1404J · DestroyedNTSB CHI77FEP28Accident · Serious injuriesClosedCessna 210D · Boscobel, WIMar 26, 1977 · N3924Y · Substantial damageNTSB CHI77FEP19Accident · Minor injuriesClosedBeech K35 · Clintonville, WIFeb 19, 1977 · N9521R · Substantial damageNTSB CHI77FEP16Accident · No injuriesClosedCessna 150 · Mosinee, WIJan 9, 1977 · N63346 · Substantial damageNTSB CHI77FEP09Accident · No injuriesClosedCessna 310 · Janesville, WIDec 2, 1976 · N188GS · Substantial damageNTSB CHI77FEP08Accident · No injuriesClosedStinson 108-1 · Burlington, WINov 25, 1976 · N97849 · Substantial damageNTSB CHI76DEP48Accident · No injuriesClosedCessna 210 · Big Falls, WISep 11, 1976 · N9450T · Substantial damageNTSB CHI76FEP38Accident · Minor injuriesClosedHughes 269A · Abbotsford, WIJul 25, 1976 · N8929F · Substantial damageNTSB CHI76FEP37Accident · No injuriesClosedSnow 600S2C · Plainfield, WIJul 21, 1976 · N1629S · DestroyedNTSB CHI76FEP35Accident · Serious injuriesClosedCessna 210 · Schofield, WIJul 13, 1976 · N9416T · DestroyedNTSB CHI76FEP31Accident · Serious injuriesClosedLuscombe 8A · West Bend, WIMay 24, 1976 · N1037K · DestroyedNTSB CHI76FER05Accident · No injuriesClosedCessna 150G · Rice Lake, WINov 15, 1975 · N3940J · Substantial damageNTSB CHI76FEP18Accident · Serious injuriesClosedBeech 95-B55 · Crandon, WINov 7, 1975 · N181Q · Substantial damageNTSB CHI76AC008Accident · 1 fatalClosedCessna 170B · Iron Ridge, WIAug 2, 1975 · N3468C · DestroyedNTSB CHI76DEP12Accident · No injuriesClosedCessna 188 · Gillman, WIJul 28, 1975 · N8135V · Substantial damageNTSB CHI76DEP10Accident · Minor injuriesClosedGrum Amer AA1-A · Portage, WIJul 20, 1975 · N9251L · Substantial damageNTSB CHI76FEP05Accident · Serious injuriesClosedBeech S3S · Franklin, WIJul 19, 1975 · N1478V · Substantial damageNTSB CHI75FEP69Accident · No injuriesClosedCessna 182H · Gratiot, WIJun 22, 1975 · N8454S · Substantial damageNTSB CHI75FEP46Accident · Minor injuriesClosedLuscombe 8A · Burlington, WIJun 14, 1975 · N45974 · Substantial damageNTSB CHI75FEP62Accident · Serious injuriesClosedSchweizer SGS2 · West Bend, WIMay 31, 1975 · N1204S · Substantial damageNTSB CHI75DEP59Accident · No injuriesClosedGlobe GC-1B · Milwaukee, WIMay 25, 1975 · N80923 · Substantial damageNTSB CHI75FEP53Accident · Minor injuriesClosedFairchild 24W-41 · Union Grove, WIMay 18, 1975 · N28504 · Substantial damageNTSB CHI75AC086Accident · No injuriesClosedPiper PA-16 · Janesville, WIMay 10, 1975 · N5382H · 2 aircraft · Minor damageNTSB CHI75DER06Accident · No injuriesClosedCessna 180 · River Falls, WIMar 28, 1975 · N9121C · Substantial damageNTSB CHI75AC073Accident · Serious injuriesClosedDouglas DC-10 · Lacrosse, WIMar 13, 1975 · N149US · No damageNTSB CHI75FEP42Accident · Serious injuriesClosedCessna 337A · Kenosha, WIMar 5, 1975 · N5358S · Substantial damageNTSB CHI75AC061Accident · 2 fatalClosedBeech 95 · Waterford, WIMar 5, 1975 · N2061C · DestroyedNTSB CHI75DER05Accident · Minor injuriesClosedCessna 182C · Superior, WIFeb 23, 1975 · N8778T · Substantial damageNTSB CHI75AC054Accident · 1 fatalClosedBeech V35BTC · Waukesha, WIJan 22, 1975 · N580A · DestroyedNTSB CHI75FEP36Accident · No injuriesClosedCessna 170B · Waukesha, WIDec 28, 1974 · N3120B · Substantial damageNTSB CHI75FEP33Accident · 2 fatalClosedCessna 150L · Slinger, WIOct 30, 1974 · N10430 · DestroyedNTSB CHI75AC036Accident · 1 fatalClosedBellanca 17-30A · Lakewood, WIOct 24, 1974 · N14746 · DestroyedNTSB CHI75FEP12Accident · Minor injuriesClosedWalts Wings S1 · Hartford, WIAug 5, 1974 · N7748 · Substantial damageNTSB CHI75FER02Accident · No injuriesClosedN. American P-51 · River Falls, WIAug 4, 1974 · N6519D · Substantial damageNTSB CHI75DEP17Accident · No injuriesClosedPiper PA-30 · Madison, WIAug 4, 1974 · N7690Y · Substantial damageNTSB CHI75FER01Accident · Minor injuriesClosedVollmer Sprts · Webster, WIAug 3, 1974 · N4646T · Substantial damageNTSB CHI75FEP10Accident · No injuriesClosedAcro Duster SA-700 · Fond Du Lac, WIJul 27, 1974 · N181J · Substantial damageNTSB CHI75FEP05Accident · Minor injuriesClosedCessna 180 · Three Lakes, WIJul 23, 1974 · N3460Y · Substantial damageNTSB CHI75AC008Accident · 3 fatalClosedCessna 310L · Land O' Lakes, WIJul 17, 1974 · N69939 · DestroyedNTSB CHI74FEP50Accident · Minor injuriesClosedPiper PA-24 · Madison, WIJun 22, 1974 · N6847P · Substantial damageNTSB CHI74AC083Accident · 1 fatalClosedBellanca 7ACA · Rhinelander, WIApr 9, 1974 · N68530 · Substantial damageNTSB CHI74AC079Accident · 1 fatalClosedCessna 150F · Glen Flora, WIMar 28, 1974 · N8654G · DestroyedNTSB CHI74AC078Accident · 1 fatalClosedEnstrom F-28A · Superior, WIMar 24, 1974 · N9592 · Substantial damageNTSB CHI74DEP34Accident · No injuriesClosedBensen B8MV · Wind Lake, WIFeb 17, 1974 · N2473 · Substantial damageNTSB CHI74AC024Accident · 6 fatalClosedPiper PA-30 · Florence, WISep 21, 1973 · N7878Y · DestroyedNTSB CHI74AC002Accident · 1 fatalClosedCessna 170A · Superior, WIJul 2, 1973 · N9745A · DestroyedNTSB CHI73FER09Accident · 1 fatalClosedMoonraker 100 · Prescott, WIJun 13, 1973 · N8669E · DestroyedNTSB CHI73FER08Accident · Serious injuriesClosedTaylorcraft BC12-D · Menomonie, WIJun 13, 1973 · N96584 · Substantial damageNTSB CHI73FEP61Accident · 1 fatalClosedCassutt 111M · Bloomer, WIMay 28, 1973 · N143L · DestroyedNTSB CHI73AC085Accident · 1 fatalClosedAeronca 7AC · Rice Lake, WIApr 25, 1973 · N85010 · Destroyed