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 CHI67F0480Accident · Minor injuriesClosedAeronca 7FC · Walbridge, OHJan 13, 1967 · N7504B · DestroyedNTSB FTW67D0460Accident · No injuriesClosedPiper PA-28 · Munday, TXJan 12, 1967 · N4174J · Substantial damageNTSB FTW67D0409Accident · No injuriesClosedPiper PA-28 · Dallas, TXJan 12, 1967 · N7578W · Substantial damageNTSB SEA67D0286Accident · No injuriesClosedCessna 150 · Broadus, MTJan 11, 1967 · N4190U · Substantial damageNTSB FTW67D0405Accident · No injuriesClosedMooney M20F · Mena, ARJan 10, 1967 · N9507M · Substantial damageNTSB NYC67A0098Accident · 9 fatalClosedBeech D18S · New Shrewsbury, NJJan 5, 1967 · N2045D · DestroyedNTSB MIA67A0075Accident · No injuriesClosedBeech D18S · Muscle Shoals, ALJan 2, 1967 · N81L · Substantial damageNTSB NYC67F0422Accident · Minor injuriesClosedAeronca 11AC · Pittsfield, MEJan 1, 1967 · N9370E · Substantial damageNTSB MKC67A0042Accident · 2 fatalClosedCessna 205 · Dighton, KSJan 1, 1967 · N1881Z · DestroyedNTSB DEN67A0039Accident · Minor injuriesClosedBeech D50B · Gallup, NMJan 1, 1967 · N5658D · Substantial damageNTSB MIA67F0348Accident · Minor injuriesClosedPiper PA-28 · Ocala, FLDec 31, 1966 · N8139W · Substantial damageNTSB DIA67F0009Accident · No injuriesClosedLuscombe 8F · Gaithersburg, MDDec 31, 1966 · N802B · Substantial damageNTSB FTW67D0406Accident · No injuriesClosedCessna 150E · Duncan, OKDec 30, 1966 · N6298T · Substantial damageNTSB MIA67F0354Accident · Minor injuriesClosedPiper PA-32 · San Juan, PRDec 29, 1966 · N3712W · Substantial damageNTSB OAK67F0216Accident · Serious injuriesClosedBoeing B75N1 · Paso Robles, CADec 28, 1966 · N67343 · DestroyedNTSB DIA67D0019Accident · No injuriesClosedPiper PA-22 · Laurel, MDDec 27, 1966 · N5413Z · Substantial damageNTSB CHI67D0384Accident · Minor injuriesClosedBeech A35 · Concord, OHDec 24, 1966 · N717B · Substantial damageNTSB NYC67F0444Accident · No injuriesClosedCessna 150 · Broadbrook, CTDec 23, 1966 · N5558E · Substantial damageNTSB NYC67D0435Accident · No injuriesClosedCessna 120 · Jobstown, NJDec 21, 1966 · N4221N · Substantial damageNTSB DEN67D0185Accident · No injuriesClosedCessna 172 · Crown Point, NMDec 20, 1966 · N4102Z · Substantial damageNTSB CHI67A0079Accident · No injuriesClosedBeech D18S · Grand Rapids, MIDec 19, 1966 · N25Q · Substantial damageNTSB FTW67F0372Accident · 1 fatalClosedGlobe GC-1B · Bellville, TXDec 12, 1966 · N80521 · DestroyedNTSB OAK67F0198Accident · Serious injuriesClosedTaylorcraft BC12-D · Fair Oaks, CADec 11, 1966 · N43515 · Substantial damageNTSB CHI67F0392Accident · Serious injuriesClosedPiper J5A · Houghton, MIDec 10, 1966 · N32787 · DestroyedNTSB MIA67F0322Accident · Serious injuriesClosedChampion 402 · W.Hollywood, FLDec 5, 1966 · N9964Y · DestroyedNTSB NYC67F0391Accident · No injuriesClosedPiper J3C-65 · Farmington, MEDec 4, 1966 · N25972 · Substantial damageNTSB FTW67F0427Accident · No injuriesClosedPiper PA-22 · Kingsville, TXDec 3, 1966 · N6135D · Substantial damageNTSB DEN67D0167Accident · No injuriesClosedCessna 150 · Logmont, CONov 30, 1966 · N3817J · Substantial damageNTSB ANC67A0048Accident · Minor injuriesClosedPiper PA-14 · Homer, AKNov 30, 1966 · N4236H · Substantial damageNTSB FTW67F0339Accident · Minor injuriesClosedPiper J3C-65 · Stuttgart, ARNov 29, 1966 · N77565 · Substantial damageNTSB FTW67F0306Accident · No injuriesClosedCessna 150A · Abilene, TXNov 26, 1966 · N6669T · DestroyedNTSB FTW67D0390Accident · No injuriesClosedPiper PA-24 · Pumpville, TXNov 26, 1966 · N6568P · Substantial damageNTSB LAX67F0207Accident · No injuriesClosedBeech 35 · Wellton, AZNov 24, 1966 · N2710V · Substantial damageNTSB FTW67F0335Accident · No injuriesClosedTaylorcraft DC0-65 · Grapeland, TXNov 24, 1966 · N50630 · Substantial damageNTSB CHI67D0399Accident · Minor injuriesClosedPiper PA-22 · Sycamore, OHNov 24, 1966 · N9641D · Substantial damageNTSB SEA67D0226Accident · No injuriesClosedBeech 35 · Madras, ORNov 23, 1966 · N3854N · Substantial damageNTSB NYC67A0083Accident · 4 fatalClosedPiper PA-23 · Lewisburg, PANov 23, 1966 · N4562P · DestroyedNTSB NYC67F0396Accident · No injuriesClosedRearwin 185 · Yorktown Hts, NYNov 21, 1966 · N93245 · Substantial damageNTSB MKC67D0346Accident · No injuriesClosedPiper PA-22 · Independence, MONov 20, 1966 · N7159D · Substantial damageNTSB NYC67A0081Accident · 3 fatalClosedPiper PA-28 · Crewe, VANov 19, 1966 · N8458W · DestroyedNTSB MIA66S0005Accident · No injuriesClosedCurtis-Wrt C-46 · Keflavick, Canary IslandsNov 19, 1966 · N68966 · Substantial damageNTSB FTW68F0246Accident · Minor injuriesClosedPiper PA-22 · Dallas, TXNov 19, 1966 · N3238B · Substantial damageNTSB FTW67A0056Accident · No injuriesClosedHiller UH-12L · Woodville, TXNov 19, 1966 · N9780C · Substantial damageNTSB MIA67F0329Accident · Serious injuriesClosedBoeing A75 · Florida City, FLNov 15, 1966 · N58226 · Substantial damageNTSB MKC67D0363Accident · No injuriesClosedAeronca 7AC · Perryville, MONov 13, 1966 · N2653E · Substantial damageNTSB MKC67D0321Accident · No injuriesClosedCessna 175 · Stratford, IANov 12, 1966 · N7116M · Substantial damageNTSB MIA67F0277Accident · No injuriesClosedCessna 150 · Somerville, TNNov 12, 1966 · N8135F · Substantial damageNTSB MIA67D0554Accident · No injuriesClosedCessna 337 · Hartselle, ALNov 12, 1966 · N6357F · Substantial damageNTSB LAX67D0253Accident · Minor injuriesClosedPiper PA-28 · Lucerne Val, CANov 12, 1966 · N5706W · Substantial damageNTSB MKC67F0337Accident · No injuriesClosedCessna 182 · Alexandria, MNNov 11, 1966 · N2690G · Substantial damage