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 CHI79FA072Accident · 2 fatalClosedPiper PA-24 · Bloomfield Twp, WISep 2, 1979 · N6051P · 2 aircraft · Substantial damageNTSB CHI79FEP75Accident · 2 fatalClosedPiper PA-28 · Kenosha, WIAug 18, 1979 · N3016H · DestroyedNTSB CHI79FEP69Accident · No injuriesClosedCessna 177 · Oshkosh, WIJul 30, 1979 · N30231 · Substantial damageNTSB CHI79FEP63Accident · No injuriesClosedCessna 182 · Green Bay, WIJul 30, 1979 · N333JB · Substantial damageNTSB CHI79FA063Accident · 1 fatalClosedPiper PA-32 · Spring Green, WIJul 29, 1979 · N20995 · DestroyedNTSB CHI79FEP66Accident · Serious injuriesClosedCessna 172M · Rockwood, WIJul 27, 1979 · N1862V · Substantial damageNTSB CHI79FEP67Accident · Minor injuriesClosedPiper PA-17 · South Byron, WIJul 15, 1979 · N4839H · Substantial damageNTSB CHI79FEP53Accident · No injuriesClosedCessna 150 · Milwaukee, WIJul 6, 1979 · N7774F · Substantial damageNTSB CHI79FEP51Accident · Serious injuriesClosedCessna 182P · Neillsville, WIJul 3, 1979 · N22FW · Substantial damageNTSB CHI79FA056Accident · 1 fatalClosedPiper PA-28 · Taylor, WIJul 3, 1979 · N8341C · DestroyedNTSB CHI79FEP48Accident · Minor injuriesClosedBeech V35 · Beloit, WIJun 8, 1979 · N2069W · Substantial damageNTSB CHI79FEP43Accident · No injuriesClosedPiper PA-32R · Iola, WIApr 13, 1979 · N9585C · Substantial damageNTSB CHI79FEP40Accident · No injuriesClosedCessna 150M · Appleton, WIApr 7, 1979 · N9365U · Substantial damageNTSB CHI79FEP39Accident · No injuriesClosedBellanca 7GCBC · Bristol, WIApr 1, 1979 · N5507K · Substantial damageNTSB CHI79FEP13Accident · No injuriesClosedCessna 150 · Sussex, WIDec 28, 1978 · N60422 · Substantial damageNTSB CHI79FEP07Accident · Minor injuriesClosedCessna P210N · Green Bay, WINov 15, 1978 · N4738P · Substantial damageNTSB CHI79FEP03Accident · No injuriesClosedBeech V-35A · Richland Cntr, WIOct 11, 1978 · N3771Q · Substantial damageNTSB CHI78FA070Accident · 1 fatalClosedCessna 150F · Portage, WISep 25, 1978 · N8815S · DestroyedNTSB CHI78FEP70Accident · No injuriesClosedPiper PA-28 · Milwaukee, WISep 15, 1978 · N32506 · Substantial damageNTSB CHI78FEP68Accident · No injuriesClosedMooney M20F · Racine, WISep 11, 1978 · N9275V · Substantial damageNTSB CHI78FEP65Accident · Serious injuriesClosedCessna 177B · Junction City, WISep 3, 1978 · N35121 · DestroyedNTSB CHI78FEP69Accident · No injuriesClosedCessna 172F · Sauk Prairie, WISep 1, 1978 · N8312U · Substantial damageNTSB CHI78FEP61Accident · 2 fatalClosedBeech C23 · Portage, WIAug 18, 1978 · N18952 · DestroyedNTSB CHI79FEP55Accident · No injuriesClosedCessna 177 · Eagle River, WIAug 2, 1978 · N29425 · Substantial damageNTSB CHI78FA051Accident · 2 fatalClosedBeech E-33A · Prt Washington, WIJul 31, 1978 · N6275V · DestroyedNTSB CHI78FEP51Accident · No injuriesClosedPiper PA-22 · Milwaukee, WIJul 28, 1978 · N8195C · Substantial damageNTSB CHI78FEP40Accident · No injuriesClosedBeech B-23 · Lacrosse, WIJul 4, 1978 · N4003T · Substantial damageNTSB CHI78FEP37Accident · No injuriesClosedDehavilland DHC-2 · Chippewa Falls, WIMay 28, 1978 · N5163G · Substantial damageNTSB CHI78FA041Accident · 4 fatalClosedCessna T210M · Bloomer, WIMay 27, 1978 · N761GV · DestroyedNTSB CHI78DEP33Accident · No injuriesClosedCessna 185 · Clyman, WIMay 10, 1978 · N7591H · Substantial damageNTSB CHI78FEP24Accident · No injuriesClosedPiper PA-28 · Lacrosse, WIMar 4, 1978 · N4547F · Substantial damageNTSB CHI78FEP19Accident · No injuriesClosedTaylorcraft BL-65 · Hartford, WIFeb 25, 1978 · N29504 · Substantial damageNTSB CHI78FEP20Accident · Minor injuriesClosedPiper PA-28R · Pewaukee, WIFeb 16, 1978 · N1394T · Substantial damageNTSB CHI78DEP12Accident · No injuriesClosedCessna 150L · Sun Prairie, WIJan 31, 1978 · N10735 · Substantial damageNTSB CHI78FA034Accident · 1 fatalClosedPiper PA-28 · Mellen, WIJan 2, 1978 · N8215C · DestroyedNTSB CHI78FEP08Accident · No injuriesClosedGrum Amer AA1B · Racine, WIOct 28, 1977 · N888IL · Substantial damageNTSB CHI77FEP61Accident · No injuriesClosedCessna 172L · West Bend, WISep 18, 1977 · N1TA · Substantial damageNTSB CHI77FEP63Accident · Serious injuriesClosedCessna 150L · Winneconne, WISep 14, 1977 · N19257 · Substantial damageNTSB CHI78FA071Accident · 2 fatalClosedCessna 320 · Milwaukee, WISep 11, 1977 · N5719X · DestroyedNTSB CHI77FEP60Accident · No injuriesClosedCessna 170 · Mosinee, WISep 5, 1977 · N2667V · Substantial damageNTSB CHI77FEP59Accident · Minor injuriesClosedBell 47J2A · Plymouth, WISep 5, 1977 · N6769D · Substantial damageNTSB CHI77FA069Accident · 4 fatalClosedCessna 170 · Florence, WIAug 23, 1977 · N4052V · DestroyedNTSB CHI77FA063Accident · 3 fatalClosedAero Comdr 720 · Wausau, WIAug 4, 1977 · N688R · DestroyedNTSB CHI77FA062Accident · 1 fatalClosedBellanca 7ACA · Oshkosh, WIAug 1, 1977 · N12204 · 2 aircraft · DestroyedNTSB CHI77EEP48Accident · 1 fatalClosedBellanca 14-13 · Prairie Du Chn, WIJul 30, 1977 · N86709 · Substantial damageNTSB CHI77FER04Accident · 1 fatalClosedPiper PA-24 · Superior, WIJul 4, 1977 · N8898 · Substantial damageNTSB CHI77DEP40Accident · No injuriesClosedBellanca 7AC · Kaukauna, WIJun 26, 1977 · N85118 · Substantial damageNTSB CHI77DEP35Accident · Serious injuriesClosedReplica BE-2C · Neenah, WIJun 14, 1977 · N1914B · Substantial damageNTSB CHI77DEP36Accident · Minor injuriesClosedPiper PA-32 · Clam Lake, WIJun 2, 1977 · N8383C · Substantial damageNTSB CHI77FA046Accident · 4 fatalClosedPiper PA-24 · Evansville, WIMay 27, 1977 · N9326P · Destroyed