NTSB investigations, UT

221 cases where the phase of flight was Approach.

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 = 500)
  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 DEN69D0027Accident · No injuriesClosedSchweizer 2-33 · Heber City, UTJun 16, 1968 · N2459W · Substantial damageNTSB DEN69D0017Accident · Minor injuriesClosedBeech J35 · Spanish Fork, UTJun 16, 1968 · N735B · Substantial damageNTSB DEN68F0123Accident · Serious injuriesClosedCessna 182 · Roosevelt, UTOct 7, 1967 · N3978D · Substantial damageNTSB DEN68F0053Accident · No injuriesClosedBeech H35 · Ogden, UTAug 2, 1967 · N5462D · Substantial damageNTSB DEN67D0186Accident · Minor injuriesClosedAeronca 7AC · Layton, UTDec 16, 1966 · N81973 · Substantial damageNTSB DEN67A0023Accident · No injuriesClosedCessna 182B · Green River, UTSep 20, 1966 · N2365G · Substantial damageNTSB DEN67A0017Accident · 2 fatalClosedAeronca 11AC · Sandy, UTAug 24, 1966 · N9775E · DestroyedNTSB DEN67D0077Accident · No injuriesClosedCessna 182 · Green River, UTAug 16, 1966 · N4918D · Substantial damageNTSB DEN67D0042Accident · No injuriesClosedBellanca 14-13 · Panguitch, UTJul 13, 1966 · N74209 · Substantial damageNTSB DEN67D0014Accident · Minor injuriesClosedCessna 182A · Dutch John, UTJun 10, 1966 · N5109D · DestroyedNTSB DCA66A0004Accident · 43 fatalClosedBoeing B-727 · Salt Lke Cty, UTNov 11, 1965 · N7030U · DestroyedNTSB DEN66A0029Accident · 1 fatalClosedBeechcraft B35 · Blanding, UTOct 19, 1965 · N5238C · DestroyedNTSB DEN66A0016Accident · Minor injuriesClosedBell 47G3B1 · Bountiful, UTAug 24, 1965 · N1169W · Substantial damageNTSB DEN66F0033Accident · No injuriesClosedCessna 175 · Goldhill, UTJul 9, 1965 · N8180T · DestroyedNTSB DEN65F0047Accident · Minor injuriesClosedCessna 182 · St George, UTApr 16, 1965 · N5490B · Substantial damageNTSB DEN65F0028Accident · No injuriesClosedForney F-1A · Spanish Fork, UTFeb 24, 1965 · N3051G · DestroyedNTSB UNK64X4167Accident · No injuriesClosedCessna 182 · Hite, UTSep 26, 1964 · N3170U · Substantial damageNTSB UNK64X3736Accident · No injuriesClosedCessna 210 · Salt Lake City, UTSep 2, 1964 · N7347E · Substantial damageNTSB UNK64X3565Accident · No injuriesClosedCessna 182 · Bullfrog Basin, UTAug 23, 1964 · N5389B · DestroyedNTSB UNK64X1653Accident · No injuriesClosedCessna 180 · West Jordan, UTMay 7, 1964 · N4658A · Substantial damageNTSB UNK64X0734Accident · No injuriesClosedPiper PA28 · Ogden, UTFeb 28, 1964 · N8559W · Substantial damage