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 MIA72FLG14Accident · No injuriesClosedPiper J3C-65 · Clio, SCOct 3, 1971 · N37949 · Substantial damageNTSB DEN72DTC08Accident · No injuriesClosedPiper PA-24 · Gillette, WYOct 3, 1971 · N5149P · Substantial damageNTSB CHI72FLQ15Accident · Serious injuriesClosedCessna A188 · Utica, KYOct 2, 1971 · N3531Q · DestroyedNTSB CHI72DEA14Accident · No injuriesClosedStinson 108-3 · Acton, INOct 2, 1971 · N6919M · DestroyedNTSB CHI72FEI18Accident · No injuriesClosedCessna 175A · Montville, OHSep 30, 1971 · N6890E · Substantial damageNTSB CHI72FEP16Incident · No injuriesClosedPiper PA-28 · Dodgeville, WISep 29, 1971 · N7251W · Minor damageNTSB MIA72FKG45Accident · Minor injuriesClosedAero-Comdr S2R · Albany, GASep 28, 1971 · N29A · DestroyedNTSB FTW72FRA10Accident · No injuriesClosedCessna 150E · Pearland, TXSep 27, 1971 · N6211R · Substantial damageNTSB DEN72DTE32Accident · No injuriesClosedMooney M20F · Flagler, COSep 26, 1971 · N6307Q · Substantial damageNTSB CHI72AC027Accident · 6 fatalClosedBeech 95-B55 · Milwaukee, WISep 25, 1971 · N8627Q · DestroyedNTSB MIA72AM029Accident · 1 fatalClosedPiper PA-28 · Augusta, GASep 22, 1971 · N8439R · DestroyedNTSB NYC72FHA16Accident · No injuriesClosedSchweizer SGS233 · Erwinna, PASep 21, 1971 · N1205S · Substantial damageNTSB MIA72FLD11Accident · Minor injuriesClosedPiper PA-24 · St.Petersburg, FLSep 21, 1971 · N9208P · Substantial damageNTSB FTW72AF016Accident · 2 fatalClosedPiper J3C-65 · Springdale, ARSep 20, 1971 · N3633K · DestroyedNTSB CHI72FEM33Accident · Minor injuriesClosedMccullough J-2 · Greenville, MISep 20, 1971 · N4313G · Substantial damageNTSB ANC72DAG47Accident · No injuriesClosedPiper J3C-65 · Anchorage, AKSep 20, 1971 · N3257N · Substantial damageNTSB NYC72FNE12Accident · Minor injuriesClosedBeech A-45 · Monroe, CTSep 19, 1971 · N450L · Substantial damageNTSB LAX72DUJ44Accident · Minor injuriesClosedBeech B23 · Kearny, AZSep 19, 1971 · N9129T · Substantial damageNTSB CHI72FEG15Accident · Serious injuriesClosedStinson 10A · Phillipsburg, OHSep 19, 1971 · N34698 · DestroyedNTSB SEA72AS013Accident · Minor injuriesClosedHughes 369HS · Shelton, WASep 18, 1971 · N9018F · Substantial damageNTSB FTW72FRD10Accident · No injuriesClosedBell 47G4A · Morgan City, LASep 18, 1971 · N4708R · Substantial damageNTSB MKC72DER22Accident · Minor injuriesClosedCessna 150J · Bemidji, MNSep 17, 1971 · N51228 · Substantial damageNTSB MIA72FLG11Accident · No injuriesClosedCessna 177 · Spartanburg, SCSep 16, 1971 · N3113T · Substantial damageNTSB CHI72FET08Accident · Minor injuriesClosedLuscombe 8A · Elkhart, INSep 16, 1971 · N71375 · Substantial damageNTSB CHI72FEG16Accident · Minor injuriesClosedPiper PA-22 · Peebles, OHSep 15, 1971 · N7016D · Substantial damageNTSB FTW72DRJ08Accident · No injuriesClosedBell 47G4A · Jonesville, LASep 14, 1971 · N4024G · Substantial damageNTSB MKC72DCJ26Accident · No injuriesClosedPiper PA-24 · Boelus, NESep 13, 1971 · N6383P · Substantial damageNTSB DEN72AD011Accident · No injuriesClosedBell 47G3B1 · Sandy, UTSep 13, 1971 · N73972 · Substantial damageNTSB MKC72FCG16Accident · Serious injuriesClosedGlobe GC-1B · Brunswick, MOSep 12, 1971 · N3843K · Substantial damageNTSB MKC72DCG21Accident · Minor injuriesClosedCessna 172 · Topeka, KSSep 12, 1971 · N1345Y · Substantial damageNTSB FTW72FPM09Accident · Minor injuriesClosedErco 415-C · Tulsa, OKSep 12, 1971 · N99252 · Substantial damageNTSB NYC72FFA14Accident · No injuriesClosedSmith Mini DSA-1 · Ravena, NYSep 10, 1971 · N4723G · Substantial damageNTSB LAX72FUJ43Accident · No injuriesClosedPiper PA-22 · Casa Grande, AZSep 10, 1971 · N3848P · Substantial damageNTSB DEN72DTM06Accident · No injuriesClosedCessna 177 · Lehi, UTSep 10, 1971 · N29319 · Substantial damageNTSB SEA72DYE13Accident · No injuriesClosedBeech A35 · Prevost, WASep 9, 1971 · N654B · Substantial damageNTSB SEA72AS010Accident · 4 fatalClosedBellanca 17-30A · Seattle, WASep 9, 1971 · N6701V · DestroyedNTSB OAK72DVD21Accident · No injuriesClosedPiper PA-24 · Hayward, CASep 9, 1971 · N9224P · Substantial damageNTSB NYC72FKM05Accident · No injuriesClosedMonocoupe 90A · Monroe, NCSep 8, 1971 · N119V · Substantial damageNTSB MIA72DKJ21Accident · No injuriesClosedPiper PA-25 · Ardmore, ALSep 8, 1971 · N7704Z · Substantial damageNTSB NYC72FHD12Accident · Serious injuriesClosedBeech A23 · Atlantic City, NJSep 7, 1971 · N1416L · Substantial damageNTSB FTW72FPD12Accident · No injuriesClosedPop Rivets 4 · Arlington, TXSep 7, 1971 · N9148 · Substantial damageNTSB NYC72DHD16Accident · No injuriesClosedLuscombe 8A · Cross Keys, NJSep 6, 1971 · N1230K · Substantial damageNTSB FTW72FPM08Accident · No injuriesClosedPiper PA-22 · Tulsa, OKSep 6, 1971 · N1651A · Substantial damageNTSB ANC72DAG48Accident · No injuriesClosedCessna 180 · Skwentna, AKSep 6, 1971 · N4653A · Substantial damageNTSB SEA72FYG09Accident · Minor injuriesClosedLuscombe 8A · Newport, WASep 5, 1971 · N28443 · Substantial damageNTSB MKC72FGD29Accident · No injuriesClosedAmer Avco AA-1 · Blakesburg, IASep 5, 1971 · N5973L · Substantial damageNTSB MIA72FLA09Accident · Minor injuriesClosedCessna 177 · Destin, FLSep 5, 1971 · N29330 · Substantial damageNTSB FTW72DPG37Accident · Minor injuriesClosedCessna 150G · Abbott, ARSep 5, 1971 · N6296S · Substantial damageNTSB NYC72FIJ29Accident · Serious injuriesClosedCessna 150 · Abingdon, VASep 4, 1971 · N5994G · DestroyedNTSB MKC72FCQ24Accident · No injuriesClosedCessna 182J · Arkansas Cty, KSSep 4, 1971 · N3596F · Substantial damage