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 ANC72AA024Accident · 1 fatalClosedChampion 7GCBC · Prescott, AZNov 20, 1971 · N9002L · DestroyedNTSB ANC72FAG76Accident · No injuriesClosedPiper PA-28 · Naknek, AKNov 18, 1971 · N257W · Substantial damageNTSB MIA72AM045Accident · 4 fatalClosedPiper PA-31 · Iuka, MSNov 17, 1971 · N780JM · DestroyedNTSB ANC72AA021Accident · No injuriesClosedCessna 180H · Gulkana, AKNov 16, 1971 · N2757X · Substantial damageNTSB MIA72FKG55Accident · Minor injuriesClosedPiper PA-22 · Wrens, GANov 14, 1971 · N884A · Substantial damageNTSB SEA72DYC23Accident · No injuriesClosedCessna 175 · Condon, ORNov 13, 1971 · N7095M · Substantial damageNTSB ANC72FAA21Accident · No injuriesClosedCessna 170B · Livengood, AKNov 13, 1971 · N2313D · Substantial damageNTSB CHI72DEG23Accident · No injuriesClosedBeech A24R · Jamestown, OHNov 12, 1971 · N104GD · Substantial damageNTSB MKC72FCJ36Accident · Minor injuriesClosedPiper PA-22 · Nehawka, NENov 10, 1971 · N2304P · DestroyedNTSB CHI72FEP21Accident · Serious injuriesClosedLuscombe 8A · Stevens Pt., WINov 10, 1971 · N45431 · Substantial damageNTSB NYC72IN064Incident · No injuriesClosedBoeing 747 · Jamaica, NYNov 8, 1971 · N735PA · Substantial damageNTSB MIA72AM042Accident · 2 fatalClosedPiper PA-22 · Watkinsville, GANov 7, 1971 · N5443Z · DestroyedNTSB FTW72FRD16Accident · No injuriesClosedBoeing E75N1 · Elton, LANov 7, 1971 · N4874N · Substantial damageNTSB FTW72FPM13Accident · No injuriesClosedBeech A23A · Maramec, OKNov 7, 1971 · N6942Q · Substantial damageNTSB FTW72FPM12Accident · Minor injuriesClosedStinson 108-1 · Calera, OKNov 7, 1971 · N97441 · Substantial damageNTSB CHI72FEE43Accident · No injuriesClosedCessna 310H · Elgin, ILNov 7, 1971 · N7171L · DestroyedNTSB LAX72FUG32Accident · Serious injuriesClosedCessna 150 · Riverside, CANov 3, 1971 · N3027J · DestroyedNTSB CHI72AC042Accident · No injuriesClosedBeech 99 · Cleveland, OHNov 3, 1971 · N1031S · Substantial damageNTSB MIA72DKT26Accident · No injuriesClosedPiper PA-25 · Ft Myers, FLNov 1, 1971 · N6976Z · Substantial damageNTSB NYC72FGM21Accident · Minor injuriesClosedPiper PA-22 · Somerset, PAOct 30, 1971 · N7423D · Substantial damageNTSB LAX72FUG30Accident · Minor injuriesClosedPiper PA-24 · El Monte, CAOct 30, 1971 · N5795P · Substantial damageNTSB MKC72DCQ31Accident · No injuriesClosedBeech B95 · Salina, KSOct 28, 1971 · N9629R · Substantial damageNTSB MIA72FLG16Accident · Serious injuriesClosedPiper PA-25 · Edisto Is, SCOct 28, 1971 · N162W · DestroyedNTSB EU172DYZ03Accident · Minor injuriesClosedBell 47G3B1 · Bharatpur, NepalOct 27, 1971 · N1330X · Substantial damageNTSB SEA72FYG18Accident · Serious injuriesClosedPiper PA-32 · College Pl, WAOct 26, 1971 · N5268S · DestroyedNTSB OAK72AP012Accident · Minor injuriesClosedCessna 150 · Franklin, CAOct 25, 1971 · N4687X · 2 aircraft · Substantial damageNTSB DEN72FTE36Accident · Minor injuriesClosedPiper PA-28 · Denver, COOct 25, 1971 · N7708N · Substantial damageNTSB DEN72AD019Accident · No injuriesClosedCessna 310B · Creede, COOct 25, 1971 · N6636B · Substantial damageNTSB MKC72FCJ34Accident · Serious injuriesClosedPiper J3C-65 · Atkinson, NEOct 24, 1971 · N73418 · DestroyedNTSB MKC72FCG31Accident · No injuriesClosedPiper PA-22 · Buffalo, MOOct 24, 1971 · N8603C · Substantial damageNTSB FTW72FRD14Accident · Minor injuriesClosedCessna 180 · Paincourtville, LAOct 24, 1971 · N91304 · Substantial damageNTSB MIA72AM041Accident · 4 fatalClosedBeech 95-B55 · St.Petersburg, FLOct 23, 1971 · N8427N · DestroyedNTSB LAX72FUQ37Accident · No injuriesClosedCessna 170B · Quartz Hill, CAOct 23, 1971 · N2807C · Substantial damageNTSB LAX72FUA18Accident · Serious injuriesClosedLuscombe 8A · Long Beach, CAOct 23, 1971 · N71695 · DestroyedNTSB NYC72AN058Accident · 2 fatalClosedFairchild M-62A · Stevensville, MDOct 21, 1971 · N91022 · DestroyedNTSB SEA72FYC21Accident · No injuriesClosedDruin Turb D-31 · Corvallis, OROct 20, 1971 · N7193 · Substantial damageNTSB LAX72DUM12Accident · No injuriesClosedWaterman W-12 · Carlsbad, CAOct 18, 1971 · N6039 · Substantial damageNTSB ANC72AA017Accident · 1 fatalClosedPiper PA-18S · Pilot Point, AKOct 18, 1971 · N7232Y · DestroyedNTSB LAX72AL025Accident · 1 fatalClosedSchemp SH-1 · Tehachapi, CAOct 17, 1971 · N7966A · DestroyedNTSB ANC72DAG65Accident · No injuriesClosedPiper PA-22 · Russian Miss, AKOct 17, 1971 · N3551A · Substantial damageNTSB FTW72FRG32Accident · Minor injuriesClosedCessna 150G · San Antonio, TXOct 11, 1971 · N2464J · Substantial damageNTSB NYC72FIA07Accident · Minor injuriesClosedPiper PA-22 · Elkton, VAOct 10, 1971 · N8699C · Substantial damageNTSB SEA72DYC20Accident · No injuriesClosedPiper PA-22 · Oregon City, OROct 9, 1971 · N7323D · Substantial damageNTSB NYC72DNC15Accident · Minor injuriesClosedLuscombe 8A · Auburn, NHOct 8, 1971 · N77877 · Substantial damageNTSB NYC72AN048Accident · 3 fatalClosedMooney M21 · Zelienople, PAOct 8, 1971 · N9472V · DestroyedNTSB FTW72FPA07Accident · No injuriesClosedHiller UH-12C · Lorena, TXOct 7, 1971 · N73875 · Substantial damageNTSB FTW72DPJ17Accident · No injuriesClosedCessna 182 · Walters, OKOct 6, 1971 · N3528F · Substantial damageNTSB MKC72DCG25Accident · No injuriesClosedCessna 140 · Independence, MOOct 4, 1971 · N76567 · Substantial damageNTSB LAX72FUQ32Accident · Minor injuriesClosedErco 415-C · Thous.Oaks, CAOct 4, 1971 · N2759H · Substantial damageNTSB MKC72DCJ29Accident · No injuriesClosedPiper PA-28 · Glenvil, NEOct 3, 1971 · N6744W · Substantial damage