NTSB investigations, OR

695 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\" = 'OR' 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 SEA80FYM06Accident · Minor injuriesClosedCessna 152 · Newberg, ORNov 23, 1979 · N67500 · Substantial damageNTSB SEA80FYM04Accident · No injuriesClosedBell 206A · Amity, ORNov 7, 1979 · N6212N · Substantial damageNTSB SEA80DYK01Accident · No injuriesClosedCessna TU206 · Klamath Falls, OROct 12, 1979 · N1204Q · Substantial damageNTSB SEA79FYM68Accident · No injuriesClosedCessna 172N · Clatskanie, ORSep 22, 1979 · N7335B · Substantial damageNTSB SEA79AA058Accident · 12 fatalClosedDouglas DC-7C · Klamath Falls, ORSep 14, 1979 · N4SW · DestroyedNTSB SEA79FYK31Accident · Minor injuriesClosedM.B.B. BO-209 · Grants Pass, ORSep 12, 1979 · N209AD · Substantial damageNTSB SEA79FYK29Accident · Minor injuriesClosedCessna 150F · Cottage Grove, ORSep 9, 1979 · N8937S · Substantial damageNTSB SEA79FA054Accident · 3 fatalClosedMooney M20C · Newberg, ORSep 1, 1979 · N9753M · DestroyedNTSB SEA79FA053Accident · 4 fatalClosedPiper PA-28 · Paisley, ORSep 1, 1979 · N2818N · DestroyedNTSB SEA79FYK27Accident · Serious injuriesClosedCessna 177RG · Redmond, ORAug 31, 1979 · N1882Q · DestroyedNTSB SEA79FYK24Accident · No injuriesClosedCessna A185F · Eugene, ORAug 25, 1979 · N93086 · Substantial damageNTSB SEA79FYM58Accident · No injuriesClosedPiper PA-18 · Heppner, ORAug 18, 1979 · N3765Z · Substantial damageNTSB SEA79FYM57Accident · Serious injuriesClosedAero Comdr S-2R · Hermiston, ORAug 16, 1979 · N4945X · Substantial damageNTSB SEA79DYM55Accident · Minor injuriesClosedHiller Acft UH-12E · Warm Spgs Res, ORJul 28, 1979 · N6ZH · DestroyedNTSB SEA80FYM54Accident · Minor injuriesClosedHughes 369D · Bay City, ORJul 25, 1979 · N58184 · Substantial damageNTSB SEA79DYM53Accident · No injuriesClosedCessna 150 · Pilot Rock, ORJul 24, 1979 · N51192 · Substantial damageNTSB SEA79FYM52Accident · Serious injuriesClosedCessna 150 · Sisters, ORJul 21, 1979 · N51321 · Substantial damageNTSB SEA79FYM51Accident · Minor injuriesClosedBell 47G · Embler, ORJul 18, 1979 · N6356X · Substantial damageNTSB SEA79FYM48Accident · No injuriesClosedGrumman G-164A · Hermiston, ORJul 11, 1979 · N5340 · Substantial damageNTSB SEA79FYM41Accident · No injuriesClosedCessna 182 · Wilsonville, ORJun 18, 1979 · N4952D · Substantial damageNTSB SEA79DYK13Accident · Minor injuriesClosedStinson 108-3 · Ashland, ORMay 25, 1979 · N6740M · Substantial damageNTSB SEA79FYM31Accident · Serious injuriesClosedBaird/Beers AMFS14 · Mcminnville, ORMay 8, 1979 · N75LB · DestroyedNTSB SEA79FYK10Accident · Minor injuriesClosedPiper PA-24 · Klamath Falls, ORMay 6, 1979 · N5846P · Substantial damageNTSB SEA79FA032Accident · 1 fatalClosedCessna 150 · John Day, ORMay 1, 1979 · N7753F · DestroyedNTSB SEA79FYM30Accident · Serious injuriesClosedPiper PA-28 · Sandy, ORApr 28, 1979 · N7142W · DestroyedNTSB SEA79FYM25Accident · Serious injuriesClosedCessna 172N · Pendleton, ORApr 2, 1979 · N737CD · Substantial damageNTSB SEA79FYM19Accident · No injuriesClosedCessna T210N · John Day, ORMar 15, 1979 · N7426N · Substantial damageNTSB SEA79DYK04Accident · No injuriesClosedCessna 152 · Burns, ORDec 28, 1978 · N45989 · Substantial damageNTSB DCA79AA005Accident · 10 fatalClosedDouglas DC-8 · Portland, ORDec 28, 1978 · N8082U · DestroyedNTSB SEA79FYM08Accident · No injuriesClosedHiller Acft Soloy · Lacomb, ORDec 22, 1978 · N172ET · Substantial damageNTSB SEA79FA017Accident · 1 fatalClosedCessna 150K · Astoria, ORDec 10, 1978 · N8339M · DestroyedNTSB SEA79FA016Accident · 1 fatalClosedPiper PA-14 · Eugene, ORDec 1, 1978 · N5013W · DestroyedNTSB SEA79FYK03Accident · Serious injuriesClosedCessna 210 · Triangle Lake, ORNov 13, 1978 · N5733J · Substantial damageNTSB SEA79FA010Accident · 3 fatalClosedPiper PA-20 · Joseph, OROct 28, 1978 · N2411A · DestroyedNTSB SEA78FA065Accident · 1 fatalClosedPiper PA-28 · Rainier, ORSep 14, 1978 · N5877U · DestroyedNTSB SEA78DYK22Accident · No injuriesClosedPiper PA-28 · Sun River, ORSep 12, 1978 · N43990 · Substantial damageNTSB SEA78FYK21Accident · No injuriesClosedCessna 182B · Azalea, ORSep 9, 1978 · N2465G · Substantial damageNTSB SEA78FA063Accident · 4 fatalClosedPiper PA-28 · Sisters, ORSep 7, 1978 · N39499 · DestroyedNTSB SEA78FA056Accident · 2 fatalClosedCessna 172XP · Eugene, ORAug 15, 1978 · N736XU · DestroyedNTSB SEA78FA055Accident · 3 fatalClosedAero Comdr 100 · Bly, ORAug 11, 1978 · N4179X · DestroyedNTSB SEA78FA039Accident · 4 fatalClosedPiper PA-28R · Baker, ORJun 24, 1978 · N40048 · DestroyedNTSB SEA78FA038Accident · 1 fatalClosedNavion A · Ashland, ORJun 23, 1978 · N91504 · DestroyedNTSB SEA78FYM39Accident · 1 fatalClosedBoeing E75N1 · John Day, ORJun 16, 1978 · N4769V · DestroyedNTSB SEA78DYK18Accident · Minor injuriesClosedCessna 150 · Millican, ORJun 2, 1978 · N11737 · Substantial damageNTSB SEA78FYM28Accident · Serious injuriesClosedBeech F33A · La Grande, ORApr 26, 1978 · N4515A · DestroyedNTSB SEA78FA025Accident · 1 fatalClosedCessna 150L · Junction City, ORApr 11, 1978 · N10627 · DestroyedNTSB SEA78DYK11Accident · No injuriesClosedBell 47G3B1 · Myrtle Creek, ORMar 6, 1978 · N73984 · Substantial damageNTSB SEA78FYK08Accident · Serious injuriesClosedPiper PA-18 · Ashland, ORFeb 7, 1978 · N83272 · DestroyedNTSB SEA78FA019Accident · 4 fatalClosedBeech 35 · Cascade Locks, ORFeb 7, 1978 · N3087V · DestroyedNTSB SEA78FYM16Accident · No injuriesClosedHiller UH-12E · Cannon Beach, ORJan 20, 1978 · N62346 · Substantial damage