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 FTW71AF081Accident · Serious injuriesClosedBell 206A · Gulf Of Mexico (Gulf of Mexico)Jun 9, 1971 · N6217N · DestroyedNTSB OAK71DVG87Accident · Serious injuriesClosedGrumman G164 · Robbins, CAJun 8, 1971 · N699Y · Substantial damageNTSB FTW71FRD51Accident · No injuriesClosedBoeing B75N1 · Elton, LAJun 8, 1971 · N47896 · Substantial damageNTSB NYC71DNC31Accident · No injuriesClosedAeronca 7DC · Berwick, MEJun 7, 1971 · N82439 · Substantial damageNTSB FTW71DQG74Accident · No injuriesClosedCessna A188A · Amarillo, TXJun 7, 1971 · N1571M · Substantial damageNTSB MIA71AM107Accident · 2 fatalClosedLuscombe 8A · Odessa, FLJun 6, 1971 · N71759 · DestroyedNTSB LAX71FVA81Accident · Minor injuriesClosedPiper PA-24 · Glenville, CAJun 6, 1971 · N7201P · DestroyedNTSB FTW71FPM30Accident · No injuriesClosedPiper PA-20 · Wagoner, OKJun 6, 1971 · N6995K · Substantial damageNTSB MKC71FER68Accident · No injuriesClosedPiper PA-28S · Roseville, MNJun 5, 1971 · N4974L · Substantial damageNTSB NYC71FGM51Accident · Serious injuriesClosedPiper PA-28R · Bedford, PAJun 4, 1971 · N4925J · Substantial damageNTSB MKC71FCA39Accident · No injuriesClosedCessna U206 · Cape Girardeau, MOJun 4, 1971 · N8031Z · Substantial damageNTSB SEA71FTA22Accident · No injuriesClosedBeech A23-19 · Terry, MTJun 2, 1971 · N4722J · Substantial damageNTSB MIA71FKH02Accident · No injuriesClosedCessna 182B · Warm Springs, GAJun 1, 1971 · N2282G · Substantial damageNTSB FTW71FRD49Accident · No injuriesClosedCessna A185E · New Orleans, LAJun 1, 1971 · N1620M · Substantial damageNTSB MKC71FER66Accident · Serious injuriesClosedBeech E35 · Park Rapids, MNMay 31, 1971 · N19FF · DestroyedNTSB MIA71FKQ36Accident · No injuriesClosedStearman A75 · Cleveland, MSMay 31, 1971 · N4878N · Substantial damageNTSB LAX71FUQ83Accident · Serious injuriesClosedBeech 95-C55 · Van Nuys, CAMay 31, 1971 · N9043S · DestroyedNTSB IAD71DKM36Accident · Minor injuriesClosedTaylorcraft BC12-D · Ramsuer, NCMay 31, 1971 · N44001 · Substantial damageNTSB CHI71FEM81Accident · No injuriesClosedCessna 120 · Holland, MIMay 30, 1971 · N3025N · Substantial damageNTSB CHI71DEE79Accident · No injuriesClosedAmer Avco AA1 · Pecatonica, ILMay 30, 1971 · N5621L · Substantial damageNTSB NYC71FHA53Accident · Serious injuriesClosedCessna 180 · Tamiment, PAMay 29, 1971 · N4520B · DestroyedNTSB FTW71FPG59Accident · Minor injuriesClosedBellanca 7ACA · Marion, ARMay 29, 1971 · N9138L · Substantial damageNTSB MKC71FTG29Accident · No injuriesClosedPiper PA-25 · Fairmount, NDMay 28, 1971 · N4845Y · Substantial damageNTSB SEA71FYE32Accident · No injuriesClosedNavion A · Silverdale, WAMay 27, 1971 · N8592H · Substantial damageNTSB FTW71FPD59Accident · No injuriesClosedCessna 150F · Fort Worth, TXMay 26, 1971 · N8260S · Substantial damageNTSB OAK71DVA84Accident · Minor injuriesClosedCessna 210 · Tulare, CAMay 25, 1971 · N9618T · Substantial damageNTSB MIA71IM102Incident · No injuriesClosedBoeing 727 · Miami, FLMay 25, 1971 · N464US · No damageNTSB MIA71DKA13Accident · No injuriesClosedCessna 150J · Ponce, PRMay 23, 1971 · N61318 · Substantial damageNTSB DEN71DTC06Accident · No injuriesClosedPiper PA-11 · Aladdin, WYMay 23, 1971 · N4955M · Substantial damageNTSB LAX71FUG85Accident · 1 fatalClosedBede BD4 · Chino, CAMay 20, 1971 · N99CC · DestroyedNTSB NYC71FFJ33Accident · No injuriesClosedStinson 108-1 · Skaneateles, NYMay 19, 1971 · N9198K · Substantial damageNTSB MKC71FCJ64Accident · No injuriesClosedSnow 600S2C · Falls City, NEMay 19, 1971 · N1692S · Substantial damageNTSB SEA71FTA21Accident · No injuriesClosedCessna 150J · Sidney, MTMay 17, 1971 · N60792 · Substantial damageNTSB MKC71DTG27Accident · No injuriesClosedPiper PA-25 · Mountain, NDMay 17, 1971 · N6228Z · Substantial damageNTSB MKC71DCQ40Accident · No injuriesClosedMooney M20F · Montezuma, KSMay 17, 1971 · N4024N · Substantial damageNTSB FTW71FQD22Accident · No injuriesClosedAvro 19 · El Paso, TXMay 17, 1971 · N7522 · Substantial damageNTSB LAX71FUJ93Accident · Minor injuriesClosedBeech 23 · Yuma, AZMay 16, 1971 · N2342Z · Substantial damageNTSB ANC71DAG86Accident · No injuriesClosedCessna 170A · Anchorage, AKMay 16, 1971 · N9956A · Substantial damageNTSB IAD71FIA21Accident · Minor injuriesClosedCessna 150 · Lusby, MDMay 15, 1971 · N6426F · Substantial damageNTSB FTW71FRA55Accident · Minor injuriesClosedCessna 170B · Angleton, TXMay 15, 1971 · N3473C · Substantial damageNTSB FTW71DRD46Accident · No injuriesClosedBoeing A75 · Vinton, LAMay 15, 1971 · N75263 · Substantial damageNTSB CHI71FEV44Accident · Serious injuriesClosedAeronca 65-TAL · Abingdon, ILMay 15, 1971 · N39373 · Substantial damageNTSB MKC71DCG70Accident · No injuriesClosedAeronca 7AC · Huntsville, MOMay 14, 1971 · N83727 · Substantial damageNTSB FTW71DPJ42Accident · No injuriesClosedPiper PA-17 · Hammon, OKMay 14, 1971 · N5001H · Substantial damageNTSB ANC71AA058Accident · No injuriesClosedHiller FH1100 · Port Heiden, AKMay 14, 1971 · N430FH · Substantial damageNTSB MKC71FCJ62Accident · No injuriesClosedAero Comdr A-9 · St Libory, NEMay 13, 1971 · N8292H · Substantial damageNTSB FTW71FRA56Accident · No injuriesClosedBeech J35 · Spring Branch, TXMay 13, 1971 · N7281B · Substantial damageNTSB DEN71FTM26Accident · Serious injuriesClosedBeech 35 · Provo, UTMay 13, 1971 · N476B · Substantial damageNTSB DEN71FTM25Accident · Serious injuriesClosedGlobe GC-1B · Spanish Fork, UTMay 13, 1971 · N80695 · DestroyedNTSB DCA71I0004Incident · No injuriesClosedBoeing B747 · Honolulu, HIMay 13, 1971 · N607US · Minor damage