NTSB investigations, UT

544 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\" = 'UT' 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 DCA80AA007Accident · 3 fatalClosedLockheed 188C · Salt Lake City, UTNov 18, 1979 · N859U · DestroyedNTSB DEN80DTM04Accident · No injuriesClosedBeech 19 · Cedar City, UTOct 20, 1979 · N2166W · Substantial damageNTSB DEN80FA004Accident · 2 fatalClosedRockwell 114 · Hanksville, UTOct 19, 1979 · N4873W · Substantial damageNTSB DEN80DTM03Accident · Serious injuriesClosedCessna 172 · Ogden, UTOct 8, 1979 · N2285E · DestroyedNTSB DEN80FTM02Accident · Minor injuriesClosedCessna P210 · Fillmore, UTOct 4, 1979 · N6006P · Substantial damageNTSB DEN79FA059Accident · 2 fatalClosedAero Comdr 114 · Green River, UTSep 1, 1979 · N4930W · Substantial damageNTSB DEN99FA057Accident · 4 fatalClosedPiper PA-28 · Fairview, UTAug 22, 1979 · N6445J · Substantial damageNTSB DEN79FA044Accident · No injuriesClosedCessna U206 · Milford, UTJun 17, 1979 · N4424Q · DestroyedNTSB DEN79FTM14Accident · Minor injuriesClosedPiper PA-22 · Panguitch, UTMay 18, 1979 · N8702C · Substantial damageNTSB DEN79FTM17Accident · Serious injuriesClosedMooney M20E · Hanksville, UTMay 13, 1979 · N79869 · Substantial damageNTSB DEN79FTM13Accident · No injuriesClosedGrumman G44A · Delta, UTMar 30, 1979 · N111W · Substantial damageNTSB DEN79FA029Accident · 2 fatalClosedPiper PA-24 · Corinne, UTMar 8, 1979 · N6914P · DestroyedNTSB DEN79FA016Accident · 5 fatalClosedCessna 421C · Spanish Forks, UTJan 9, 1979 · N5469G · Substantial damageNTSB DEN79FTM08Accident · Serious injuriesClosedCessna 172 · Cedar Fort, UTDec 27, 1978 · N1173U · Substantial damageNTSB DEN79FTM06Accident · Minor injuriesClosedAerospatial SA315B · Magna, UTDec 21, 1978 · N10067 · Substantial damageNTSB DEN79FA011Accident · Serious injuriesClosedAerospatial SA316B · Ogden, UTDec 14, 1978 · N8622 · DestroyedNTSB DEN79DTM02Accident · Minor injuriesClosedCessna 150 · Price, UTNov 19, 1978 · N18687 · Substantial damageNTSB DEN79FA004Accident · 1 fatalClosedCessna 172 · Provo, UTOct 20, 1978 · N1333Y · DestroyedNTSB DEN78FTM32Accident · Minor injuriesClosedCessna 150 · Heber, UTAug 17, 1978 · N51338 · Substantial damageNTSB DEN78FA061Accident · Serious injuriesClosedCessna 206 · Salina, UTAug 14, 1978 · N8009Z · Substantial damageNTSB DEN78FA059Accident · 6 fatalClosedBeech 55 · Fairfield, UTAug 13, 1978 · N978V · DestroyedNTSB DEN78FTM28Accident · 1 fatalClosedClark Focke 190A3 · Cedar City, UTJul 8, 1978 · N23FW · DestroyedNTSB DEN78FTM24Accident · Serious injuriesClosedErco 415-C · Eureka, UTJun 8, 1978 · N94153 · DestroyedNTSB DEN78FA041Accident · No injuriesClosedBell 206B3 · Westwater, UTMay 9, 1978 · N16873 · Substantial damageNTSB DEN78DTM19Accident · Minor injuriesClosedMooney M20C · Kanab, UTMay 3, 1978 · N6489U · Substantial damageNTSB DEN78FTM18Accident · No injuriesClosedCessna 150 · Fairview, UTMay 1, 1978 · N18221 · Substantial damageNTSB DEN78FA035Accident · 1 fatalClosedPiper PA-38 · Park City, UTApr 7, 1978 · N9360T · DestroyedNTSB DEN78FTM14Accident · Serious injuriesClosedPiper PA-28 · Brigham City, UTFeb 22, 1978 · N32188 · Substantial damageNTSB DEN78FTM11Accident · 1 fatalClosedTurner T-40 · Ogden, UTJan 29, 1978 · N862HC · DestroyedNTSB DEN78FA021Accident · 4 fatalClosedCessna 177B · Salt Lake City, UTJan 22, 1978 · N18133 · Substantial damageNTSB DCA78AA005Accident · 3 fatalClosedDouglas DC-8F · Kaysville, UTDec 18, 1977 · N8047U · DestroyedNTSB DEN77DTM32Accident · No injuriesClosedPiper PA-18 · Ogden, UTOct 2, 1977 · N3918Z · Substantial damageNTSB DEN77FTM32Accident · No injuriesClosedPiper PA-22 · Ogden, UTSep 28, 1977 · N6915B · Substantial damageNTSB DEN77FTM28Accident · Serious injuriesClosedPiper PA-32R · Heber City, UTAug 22, 1977 · N4331F · DestroyedNTSB DEN77DTC24Accident · Minor injuriesClosedLuscombe 8F · Dutch John, UTAug 6, 1977 · N1341B · Substantial damageNTSB DEN77FTM17Accident · Serious injuriesClosedCessna 172M · Serviers, UTMay 28, 1977 · N4200Q · DestroyedNTSB DEN77FA038Accident · 2 fatalClosedBellanca 7KCAB · Morgan, UTApr 29, 1977 · N76CC · DestroyedNTSB DEN77FTM13Accident · Minor injuriesClosedGrum Amer AA-1B · American Fork, UTApr 24, 1977 · N9565L · DestroyedNTSB DEN77FA035Accident · 2 fatalClosedCessna 182H · Tooele, UTApr 8, 1977 · N8390S · DestroyedNTSB DEN77FTM12Accident · Serious injuriesClosedNa Rockwell 114 · Vernal, UTMar 12, 1977 · N4735W · DestroyedNTSB DEN77FA027Accident · 3 fatalClosedCessna U206D · Hanna, UTFeb 26, 1977 · N72308 · DestroyedNTSB DEN77FTM10Accident · No injuriesClosedCessna 150L · Price, UTJan 31, 1977 · N6813G · Substantial damageNTSB DEN77DTM09Accident · No injuriesClosedAerospatle SA315B · Thistle, UTJan 31, 1977 · N67103 · Substantial damageNTSB DEN77FTM06Accident · Minor injuriesClosedBell 47G · Beaver, UTOct 15, 1976 · N178B · DestroyedNTSB LAX77IA003Incident · Serious injuriesClosedBoeing 737 · Salt Lake City, UTOct 5, 1976 · N4523W · No damageNTSB DEN76AD073Accident · 2 fatalClosedBell 47G3B2 · Mantua, UTSep 25, 1976 · N2205W · DestroyedNTSB DEN76AD061Accident · 1 fatalClosedPiper PA-30 · Kings Peak, UTAug 1, 1976 · N7591Y · DestroyedNTSB DEN76AD055Accident · 1 fatalClosedSchweizer SGU222 · Heber City, UTJul 8, 1976 · N2769R · DestroyedNTSB DEN76AD056Accident · 4 fatalClosedStinson 108-2 · Kamas, UTJun 29, 1976 · N8015K · DestroyedNTSB DEN76DTM37Accident · No injuriesClosedPiper PA-28 · Hatch, UTJun 23, 1976 · N5818U · Substantial damage