NTSB investigations

20,559 cases where the phase of flight was Initial climb.

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.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 350)
  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 CHI68A0106Accident · 2 fatalClosedPiper PA-28 · Bremen, OHJun 12, 1968 · N9926W · DestroyedNTSB FTW69F0013Accident · Serious injuriesClosedCessna 305A · Stuttgart, ARJun 10, 1968 · N1170D · DestroyedNTSB MKC69D0033Accident · No injuriesClosedCessna 170B · Mahaska, KSJun 9, 1968 · N3114A · Substantial damageNTSB FTW69D0024Accident · No injuriesClosedCallair A-9 · Santa Anna, TXJun 9, 1968 · N8263H · Substantial damageNTSB NYC69F0029Accident · No injuriesClosedPiper J-3 · Aliquippa, PAJun 8, 1968 · N51524 · Substantial damageNTSB MKC69F0025Accident · No injuriesClosedChampion 7GCAA · Lake Elmo, MNJun 8, 1968 · N2626G · Substantial damageNTSB MIA68F0551Accident · No injuriesClosedPiper PA-28 · St.Cloud, FLJun 8, 1968 · N4209J · Substantial damageNTSB MKC69D0011Accident · No injuriesClosedPiper PA-24 · Henderson, IAJun 7, 1968 · N6266P · Substantial damageNTSB DIA68F0235Accident · No injuriesClosedMooney M20C · N.Cumberland, WVJun 6, 1968 · N1321W · Substantial damageNTSB DEN69F0010Accident · No injuriesClosedBell 47G-3B · Baggs, WYJun 6, 1968 · N8532F · Substantial damageNTSB SEA68F0315Accident · No injuriesClosedPiper PA-25 · Cut Bank, MTJun 5, 1968 · N7291Z · Substantial damageNTSB UNK68X0146Accident · Minor injuriesClosedPiper PA-12 · Tegau Lake, CanadaJun 3, 1968 · N4025M · Substantial damageNTSB DIA69D0086Accident · No injuriesClosedPiper PA-22 · Parkersburg, WVJun 3, 1968 · N3578A · Substantial damageNTSB DEN68D0271Accident · No injuriesClosedBeech V35 · Solano, NMJun 3, 1968 · N7145N · Substantial damageNTSB CHI68F0769Accident · No injuriesClosedBeech B35 · Hobart, INJun 3, 1968 · N8779A · Substantial damageNTSB NYC69F0003Accident · Serious injuriesClosedChampion 7GCAA · Ithaca, NYJun 2, 1968 · N5169T · DestroyedNTSB NYC69D0006Accident · Minor injuriesClosedPiper PA-22 · Old Forge, NYJun 2, 1968 · N2376A · Substantial damageNTSB MKC69F0037Accident · Minor injuriesClosedBensen B8M · Lake Winnebago, MOJun 2, 1968 · N7520 · Substantial damageNTSB MKC69D0043Accident · No injuriesClosedPiper PA-28 · Ellsworth, NEJun 2, 1968 · N5953W · Substantial damageNTSB DIA68A0051Accident · 2 fatalClosedPiper PA-18 · Asheville, NCJun 2, 1968 · N292T · DestroyedNTSB CHI68F0778Accident · No injuriesClosedCessna 175 · Circleville, OHJun 2, 1968 · N7627M · Substantial damageNTSB MIA68A0099Accident · 3 fatalClosedCessna 411 · Vidalia, GAJun 1, 1968 · N7395U · DestroyedNTSB LAX68A0104Accident · 3 fatalClosedPiper PA-30 · Ontario, CAJun 1, 1968 · N7100Y · DestroyedNTSB FTW68F0616Accident · No injuriesClosedBoeing A75N1 · Beaumont, TXJun 1, 1968 · N60885 · 2 aircraft · Substantial damageNTSB ANC68A0050Accident · No injuriesClosedBell 47G-2 · Cape Yakatga, AKJun 1, 1968 · N6751D · Substantial damageNTSB MKC68D0589Accident · No injuriesClosedCessna 150 · Syracuse, NEMay 31, 1968 · N6937F · Substantial damageNTSB MKC69F0019Accident · Serious injuriesClosedRyan ST-3KR · Papillion, NEMay 30, 1968 · N48758 · DestroyedNTSB LAX69F0016Accident · 1 fatalClosedPiper PA-18 · Seligman, AZMay 30, 1968 · N5656Y · DestroyedNTSB FTW68A0102Accident · No injuriesClosedCessna 177 · Alpine, TXMay 30, 1968 · N2267Y · Substantial damageNTSB FTW68A0101Accident · No injuriesClosedCessna 337B · Norman, OKMay 30, 1968 · N2308S · 2 aircraft · Substantial damageNTSB DEN69F0002Accident · No injuriesClosedPiper PA-25 · Roswell, NMMay 30, 1968 · N6092Z · Substantial damageNTSB FTW68A0099Accident · 2 fatalClosedBeech 95-C55 · San Antonio, TXMay 29, 1968 · N2090W · DestroyedNTSB DEN68F0266Accident · No injuriesClosedBell 47G3B1 · Capitan, NMMay 29, 1968 · N1320X · Substantial damageNTSB MIA69F0001Accident · No injuriesClosedCessna 150G · Geraldine, ALMay 28, 1968 · N3196J · Substantial damageNTSB MIA68D0536Accident · No injuriesClosedCessna 182H · Hattiesburg, MSMay 27, 1968 · N8448S · Substantial damageNTSB FTW68F0298Accident · No injuriesClosedPiper PA-24 · Burleson, TXMay 27, 1968 · N7994P · DestroyedNTSB DIA68A0050Accident · 1 fatalClosedPiper PA-30 · New Mrtnsvil, WVMay 27, 1968 · N7996Y · DestroyedNTSB NYC68A0122Accident · 4 fatalClosedNavion A · Greenwich, CTMay 26, 1968 · N4918K · DestroyedNTSB FTW68D0613Accident · Minor injuriesClosedCallair A-4 · Mill Creek, OKMay 26, 1968 · N6042C · DestroyedNTSB DEN68F0281Accident · No injuriesClosedPiper PA-18A · Roswell, NMMay 26, 1968 · N3776P · DestroyedNTSB DEN68D0282Accident · Minor injuriesClosedCessna 177 · Datil, NMMay 26, 1968 · N3413T · Substantial damageNTSB UNK68X0141Accident · No injuriesClosedBell 47G-2 · Alberta, CanadaMay 24, 1968 · N6141 · DestroyedNTSB SEA69F0030Accident · No injuriesClosedCessna 172 · Renton, WAMay 24, 1968 · N6374E · Substantial damageNTSB LAX68A0102Accident · Minor injuriesClosedPiper PA-28 · Santa Monica, CAMay 24, 1968 · N3920T · Substantial damageNTSB MKC68A0067Accident · No injuriesClosedPiper PA-32 · Ft Leonrd Wood, MOMay 22, 1968 · N3538W · Substantial damageNTSB CHI69F0003Accident · Serious injuriesClosedHughes 269B · Dayton, OHMay 21, 1968 · N9479F · DestroyedNTSB MKC69F0004Accident · No injuriesClosedBeech A23-19 · Millard, NEMay 20, 1968 · N4753J · Substantial damageNTSB MKC68D0558Accident · No injuriesClosedCallair A-9 · Sublette, KSMay 20, 1968 · N9657Z · Substantial damageNTSB MIA68F0537Accident · Serious injuriesClosedPiper J3C-65 · Glen Allen, MSMay 20, 1968 · N70734 · DestroyedNTSB LAX69D0008Accident · No injuriesClosedPiper PA-20 · Big Bear Lake, CAMay 20, 1968 · N7060K · Substantial damage