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 LAX72FUG06Accident · Serious injuriesClosedCessna 210A · Claremont, CAJul 23, 1971 · N9529X · Substantial damageNTSB OAK72AP002Accident · No injuriesClosedPiper PA-32 · Alturas, CAJul 22, 1971 · N4115W · Substantial damageNTSB MKC72DER07Accident · No injuriesClosedCessna 150 · Princeton, MNJul 22, 1971 · N3653J · Substantial damageNTSB FTW72AF007Accident · 2 fatalClosedAerostar 601 · Fort Worth, TXJul 22, 1971 · N7480S · DestroyedNTSB OAK72DVW04Accident · No injuriesClosedPiper PA-28 · Shoshone, CAJul 21, 1971 · N4840L · Substantial damageNTSB LAX72AL007Accident · 2 fatalClosedRyan ST-3KR · Brmuda Duns, CAJul 21, 1971 · N46745 · DestroyedNTSB DEN72DQA07Accident · No injuriesClosedPiper PA-28R · Carrizozo, NMJul 21, 1971 · N4996J · Substantial damageNTSB CHI72FEC06Accident · Serious injuriesClosedPiper J3C-65 · Horton, MIJul 21, 1971 · N61903 · DestroyedNTSB ANC72AA001Accident · 3 fatalClosedBell 206 · Fairbanks, AKJul 21, 1971 · N7893S · DestroyedNTSB MKC72FCA02Accident · 1 fatalClosedPiper PA-25 · Old Monroe, MOJul 20, 1971 · N6619Z · DestroyedNTSB IAD72AI002Accident · 4 fatalClosedPiper PA-30 · Huntington, WVJul 20, 1971 · N7276Y · DestroyedNTSB ANC72DAG11Accident · Serious injuriesClosedHiller 12-E4 · Chitina, AKJul 20, 1971 · N9759C · Substantial damageNTSB ANC72DAG30Accident · No injuriesClosedPiper PA-20 · King Salmon, AKJul 19, 1971 · N1818A · Substantial damageNTSB OAK72FVG08Accident · Serious injuriesClosedPiper PA-28R · Reddin, CAJul 18, 1971 · N4950J · DestroyedNTSB MKC72DCJ07Accident · Serious injuriesClosedCessna 172 · Oneill, NEJul 17, 1971 · N7336A · Substantial damageNTSB DEN72FQD03Accident · No injuriesClosedNavion G · Carlsbad, NMJul 17, 1971 · N2410T · Substantial damageNTSB CHI72FEA07Accident · No injuriesClosedPiper PA-25 · Roachdale, INJul 17, 1971 · N8681L · Substantial damageNTSB NYC72AN006Accident · Minor injuriesClosedDouglas DC-3 · Windsor Lks, CTJul 16, 1971 · N74844 · Substantial damageNTSB NYC72FHA04Accident · Serious injuriesClosedPiper PA-23 · Sussex, NJJul 14, 1971 · N1251P · Substantial damageNTSB FTW72AF004Accident · No injuriesClosedBell 47G4A · Morgan City, LAJul 14, 1971 · N4708R · Substantial damageNTSB DEN72FQD02Accident · No injuriesClosedBeech 35 · Carlsbad, NMJul 14, 1971 · N5065C · Substantial damageNTSB DEN72FQD01Accident · Minor injuriesClosedPiper PA-28 · Las Cruces, NMJul 14, 1971 · N6304W · Substantial damageNTSB MIA72FLD02Accident · No injuriesClosedCessna 150 · St.Petersburg, FLJul 13, 1971 · N3524J · Substantial damageNTSB MKC72DGD40Accident · No injuriesClosedFournier RF4D · Sibley, IAJul 12, 1971 · N7752 · Substantial damageNTSB MKC72FTG04Accident · No injuriesClosedCessna 172 · Des Lacs, NDJul 11, 1971 · N9880G · Substantial damageNTSB FTW72DPG04Accident · Minor injuriesClosedPitts Spcl S1C · West Helena, ARJul 11, 1971 · N111JM · Substantial damageNTSB ANC72DAG20Accident · No injuriesClosedCessna 182 · Naknek, AKJul 11, 1971 · N5117D · Substantial damageNTSB MKC72FCG04Accident · No injuriesClosedCessna 140 · Lee'S Summit, MOJul 10, 1971 · N89384 · Substantial damageNTSB MIA72FKJ03Accident · Serious injuriesClosedPiper J3L-65 · Dothan, ALJul 10, 1971 · N27120 · Substantial damageNTSB FTW72FPG06Accident · No injuriesClosedSnow 600S2C · Waldenburg, ARJul 10, 1971 · N1657S · Substantial damageNTSB OAK72DVG04Accident · No injuriesClosedCessna 150 · Weaverville, CAJul 9, 1971 · N60945 · Substantial damageNTSB CHI72FEG01Accident · Serious injuriesClosedCessna 150J · Hillsboro, OHJul 9, 1971 · N60265 · DestroyedNTSB CHI72FEA03Accident · Minor injuriesClosedPiper J4A · Cloverdale, INJul 9, 1971 · N25733 · Substantial damageNTSB CHI72FEA01Accident · No injuriesClosedBoeing A75N1 · Greensburg, INJul 8, 1971 · N49954 · Substantial damageNTSB ANC72DAG08Accident · No injuriesClosedChampion 7GCBC · Anchorage, AKJul 8, 1971 · N1611G · Substantial damageNTSB LAX72AL003Accident · 2 fatalClosedCessna 310D · Apple Vally, CAJul 7, 1971 · N6833T · DestroyedNTSB MKC72FTG03Accident · 1 fatalClosedN.American AT-6G · Stanley, NDJul 6, 1971 · N2822G · DestroyedNTSB FTW72AF001Accident · 1 fatalClosedCessna 177 · El Dorado, ARJul 5, 1971 · N2309Y · DestroyedNTSB CHI72FEV02Accident · No injuriesClosedGrumman G-164A · Arthur, ILJul 5, 1971 · N7847 · DestroyedNTSB LAX72FUG04Accident · Serious injuriesClosedCessna 140 · Parker Dam, CAJul 4, 1971 · N89423 · DestroyedNTSB IAD72DIJ25Accident · No injuriesClosedBensen B8M-AX · Hamlin, WVJul 4, 1971 · N30067 · Substantial damageNTSB CHI72FEM02Accident · Serious injuriesClosedCessna 150F · Hessel, MIJul 4, 1971 · N8151S · DestroyedNTSB ANC72DAG23Accident · No injuriesClosedPiper PA-18 · Willow, AKJul 4, 1971 · N1009A · Substantial damageNTSB SEA72FYE02Accident · Minor injuriesClosedCessna 150 · Bellevue, WAJul 3, 1971 · N3139X · DestroyedNTSB SEA72FYC02Accident · Serious injuriesClosedBeech 23 · Seaside, ORJul 3, 1971 · N8707M · DestroyedNTSB NYC72FHA03Accident · No injuriesClosedBell 47G · Long Pond, PAJul 3, 1971 · N973B · Substantial damageNTSB LAX72FUG01Accident · No injuriesClosedPiper PA-24 · Laverne, CAJul 3, 1971 · N9082P · DestroyedNTSB LAX72DUG03Accident · No injuriesClosedChampion 7ECA · Banning, CAJul 3, 1971 · N2520F · Substantial damageNTSB FTW72FPM01Accident · Serious injuriesClosedCessna 150 · Wagoner, OKJul 3, 1971 · N50778 · Substantial damageNTSB DEN72FTE01Accident · Serious injuriesClosedCessna 150 · Aurora, COJul 3, 1971 · N8403J · Substantial damage