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 DEN76FTM27Accident · Serious injuriesClosedCessna 182P · Hanksville, UTApr 30, 1976 · N5603J · Substantial damageNTSB DEN76AD033Accident · 4 fatalClosedGrum Amer AA-5 · Hyrum, UTFeb 13, 1976 · N1357R · DestroyedNTSB DEN76FTM17Accident · Serious injuriesClosedCessna T337C · Salt Lake City, UTFeb 1, 1976 · N2496S · DestroyedNTSB DEN76DTM15Accident · No injuriesClosedCessna 172 · Ephraim, UTJan 30, 1976 · N1741F · Substantial damageNTSB DEN76AD027Accident · 3 fatalClosedPiper PA-28 · Ibapah, UTJan 3, 1976 · N44600 · DestroyedNTSB DEN76AD024Accident · 4 fatalClosedCessna 172M · Beaver, UTDec 6, 1975 · N13730 · DestroyedNTSB DEN76FTM11Accident · Serious injuriesClosedPiper PA-24 · Salt Lake City, UTOct 24, 1975 · N8922P · Substantial damageNTSB DEN76FTM07Accident · Minor injuriesClosedBerkshire 70 · Heber City, UTSep 20, 1975 · N4KX · DestroyedNTSB DEN76AD011Accident · 4 fatalClosedPiper PA-28 · Mountain Home, UTAug 22, 1975 · N8254W · DestroyedNTSB DEN76FTM04Accident · No injuriesClosedCessna 150 · Cedar City, UTAug 19, 1975 · N66670 · DestroyedNTSB DEN76FTM05Accident · No injuriesClosedDehavilland DHC-2 · Ogden, UTAug 15, 1975 · N5317G · Substantial damageNTSB DEN7LAD007Accident · 2 fatalClosedPiper PA-22 · Fremont, UTAug 1, 1975 · N3567Z · DestroyedNTSB DEN76AD001Accident · 4 fatalClosedGrum Amer AA5-B · Salt Lake City, UTJul 4, 1975 · N1525R · DestroyedNTSB DEN7KAD063Accident · 3 fatalClosedCessna 172M · Manila, UTJun 27, 1975 · N1431V · DestroyedNTSB DEN75AD060Accident · 1 fatalClosedCessna 150 · Logan, UTJun 16, 1975 · N1333Q · DestroyedNTSB DEN75AD046Accident · 4 fatalClosedBellanca 17-30A · Delle, UTMar 8, 1975 · N39871 · DestroyedNTSB DEN75DTM12Accident · No injuriesClosedPiper PA-12 · Green River, UTJan 19, 1975 · N3010M · Substantial damageNTSB DEN75AD027Accident · 1 fatalClosedRockwell 690A · Kingston, UTNov 12, 1974 · N40MP · 2 aircraft · DestroyedNTSB DEN75AD025Accident · 2 fatalClosedAero Comdr 560 · Bicknell, UTNov 2, 1974 · N867G · DestroyedNTSB DEN75AD019Accident · 4 fatalClosedPiper PA-28 · St.George, UTOct 3, 1974 · N9039W · DestroyedNTSB DEN75FTM06Accident · No injuriesClosedCessna 182B · Sandy, UTSep 28, 1974 · N7169E · Substantial damageNTSB DEN75AD015Accident · 2 fatalClosedCessna 182J · Indianola, UTSep 25, 1974 · N2755F · DestroyedNTSB DEN75AD009Accident · 1 fatalClosedBell 47G3B1 · Ogden, UTSep 5, 1974 · N73972 · DestroyedNTSB DEN75FTM03Accident · Serious injuriesClosedPiper PA-18 · Tremonton, UTJul 2, 1974 · N3351A · Substantial damageNTSB DEN74DTM16Accident · Minor injuriesClosedCessna 150H · Nephi, UTJun 9, 1974 · N6942S · DestroyedNTSB DEN74FTM18Accident · Serious injuriesClosedPiper PA-22 · Bountiful, UTJun 2, 1974 · N5363Z · Substantial damageNTSB DEN74AD045Accident · 4 fatalClosedPiper PA-22 · Cedar City, UTMar 23, 1974 · N8490D · DestroyedNTSB DEN74FTM11Accident · Serious injuriesClosedCessna 210F · Cedar City, UTMar 2, 1974 · N1882F · Substantial damageNTSB DEN74DTM12Accident · No injuriesClosedCessna 172D · Thistle, UTMar 1, 1974 · N2362U · Substantial damageNTSB DEN74AD034Accident · 6 fatalClosedBeech 60 · Hatch, UTJan 20, 1974 · N35D · DestroyedNTSB DEN74AD030Accident · 1 fatalClosedCessna 320 · Spanish Fork, UTDec 2, 1973 · N5204X · DestroyedNTSB DEN74AD029Accident · 2 fatalClosedBeech A35 · Plain City, UTDec 1, 1973 · N8512A · DestroyedNTSB DEN74AD026Accident · 2 fatalClosedCessna 421A · Roosevelt, UTNov 21, 1973 · N2217Q · DestroyedNTSB DEN74AD020Accident · 2 fatalClosedPiper PA-34 · Levan, UTNov 2, 1973 · N56025 · DestroyedNTSB DEN74AD019Accident · 1 fatalClosedPiper PA-28R · Salt Lake City, UTOct 29, 1973 · N3735T · DestroyedNTSB DEN74AD017Accident · 2 fatalClosedCessna 177B · West Delta, UTOct 11, 1973 · N34319 · DestroyedNTSB DEN74FTM06Accident · Minor injuriesClosedWittman W-8 · Salt Lake City, UTOct 4, 1973 · N11511 · Substantial damageNTSB DEN74FTM04Accident · No injuriesClosedCessna 177A · Ogden, UTAug 4, 1973 · N30367 · Substantial damageNTSB DEN74FTM02Accident · Serious injuriesClosedMooney M20C · Hurricane Mesa, UTJul 22, 1973 · N9557M · DestroyedNTSB DEN74FTM01Accident · Serious injuriesClosedPiper PA-28 · Cisco, UTJul 7, 1973 · N98087 · Substantial damageNTSB DEN73DTM31Accident · No injuriesClosedHiller Acft UH-12E · Richfield, UTJun 17, 1973 · N5332V · DestroyedNTSB DEN73AD054Accident · 1 fatalClosedChampion 7GCAA · Blanding, UTJun 10, 1973 · N3039G · DestroyedNTSB DEN73AD050Accident · 4 fatalClosedCessna A185F · Moab, UTMay 19, 1973 · N70298 · DestroyedNTSB DEN73FTM26Accident · No injuriesClosedCessna 182 · Sandy, UTApr 25, 1973 · N2306G · DestroyedNTSB DEN73DTM19Accident · No injuriesClosedCessna 172 · Vernal, UTFeb 10, 1973 · N7022A · Substantial damageNTSB DEN73AD075Accident · 1 fatalClosedBeech K35 · Salt Lake City, UTFeb 7, 1973 · N6023E · DestroyedNTSB DEN73FTM18Accident · No injuriesClosedPiper PA-24 · Bountiful, UTFeb 5, 1973 · N6594P · Substantial damageNTSB DEN73DTM16Accident · No injuriesClosedCessna 150G · Tooele, UTJan 27, 1973 · N8447G · Substantial damageNTSB DEN73DTM13Accident · Minor injuriesClosedCessna 310B · Cedar City, UTDec 4, 1972 · N6645B · Substantial damageNTSB DEN73AD017Accident · 4 fatalClosedMooney M20C · Monticello, UTOct 18, 1972 · N5971Q · Destroyed