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 CHI72FLQ28Accident · No injuriesClosedBeech H35 · Louisville, KYMar 9, 1972 · N5495D · Substantial damageNTSB CHI72FEE64Accident · No injuriesClosedCessna 177 · Richton Park, ILMar 9, 1972 · N3259T · DestroyedNTSB SEA72DYA25Accident · No injuriesClosedBeech G35 · Jordan Vally, ORMar 6, 1972 · N4490D · Substantial damageNTSB BAH72ATC25Accident · 2 fatalClosedCessna 207 · San Salvdor, Burkina FasoMar 4, 1972 · N1607U · DestroyedNTSB ANC72DAG99Accident · No injuriesClosedBensen B8M · Palmer, AKMar 4, 1972 · N574A · Substantial damageNTSB NYC72AN106Accident · 1 fatalClosedAero Comdr 500-B · Rochester, NYMar 3, 1972 · N6298X · DestroyedNTSB NYC72AN104Accident · 2 fatalClosedCessna 421B · East Boston, MAMar 2, 1972 · N456CB · DestroyedNTSB LAX72FUQ64Accident · Minor injuriesClosedAeronca 7AC · Simi, CAMar 2, 1972 · N3651E · Substantial damageNTSB CHI72FEA30Accident · No injuriesClosedPiper PA-23 · Kokomo, INFeb 27, 1972 · N1325P · DestroyedNTSB OAK72DVA49Accident · No injuriesClosedBoeing E75 · Tulare, CAFeb 26, 1972 · N61871 · Substantial damageNTSB MIA72FKG74Accident · No injuriesClosedCessna 172H · Ephesus, GAFeb 26, 1972 · N1438F · Substantial damageNTSB DEN72AD037Accident · 4 fatalClosedBeech A35 · Parker, AZFeb 26, 1972 · N221DS · DestroyedNTSB OAK72FVD33Accident · Minor injuriesClosedBeech A23 · Concord, CAFeb 25, 1972 · N7956L · DestroyedNTSB FTW72FQG72Accident · No injuriesClosedCessna 177 · Tulia, TXFeb 24, 1972 · N29302 · Substantial damageNTSB NYC72FNC28Accident · Minor injuriesClosedPiper PA-32 · Nashua, NHFeb 23, 1972 · N3768W · Substantial damageNTSB NYC72FKM23Accident · No injuriesClosedCessna 150L · Gastonia, NCFeb 22, 1972 · N6655G · Substantial damageNTSB FTW72DRG50Accident · No injuriesClosedBeech E35 · Lereto, MexicoFeb 21, 1972 · N3200C · Substantial damageNTSB MKC72AK049Accident · 1 fatalClosedPiper PA-11 · Moberly, MOFeb 20, 1972 · N4512M · DestroyedNTSB FTW72DPD29Accident · Minor injuriesClosedCessna 150 · Wingate, TXFeb 20, 1972 · N60671 · DestroyedNTSB MIA72DLG25Accident · Minor injuriesClosedCessna 172B · Greenville, SCFeb 19, 1972 · N6982X · Substantial damageNTSB SEA72DYI15Accident · Minor injuriesClosedAeronca L-3 · Grants Pass, ORFeb 18, 1972 · N53221 · Substantial damageNTSB OAK72FVM25Accident · No injuriesClosedCessna 182 · San Carlos, CAFeb 18, 1972 · N42007 · Substantial damageNTSB OAK72DVA52Accident · No injuriesClosedMccullough J-2 · Porterville, CAFeb 18, 1972 · N4341G · Substantial damageNTSB CHI72AC065Accident · 2 fatalClosedBeech D18S · Jackson, MIFeb 16, 1972 · N80398 · DestroyedNTSB MIA72FKQ29Accident · No injuriesClosedCessna 310K · Tupelo, MSFeb 15, 1972 · N6996L · DestroyedNTSB LAX72DVA44Accident · No injuriesClosedBoeing N2S-1 · Bakersfield, CAFeb 15, 1972 · N6201 · Substantial damageNTSB FTW72FQG30Accident · No injuriesClosedPiper PA-18A · Friona, TXFeb 15, 1972 · N7496D · Substantial damageNTSB OAK72DVG37Accident · No injuriesClosedPiper PA-28R · Napa, CAFeb 14, 1972 · N4306T · Substantial damageNTSB OAK72FXQ21Accident · Serious injuriesClosedPiper PA-28 · Reno, NVFeb 13, 1972 · N7237J · Substantial damageNTSB MKC72DCQ41Accident · No injuriesClosedMotor Spatz SF24 · Dodge City, KSFeb 13, 1972 · N41FA · Substantial damageNTSB ANC72DAG92Accident · No injuriesClosedPiper PA-18 · Chitina, AKFeb 13, 1972 · N7201Z · Substantial damageNTSB NYC72FFJ28Accident · No injuriesClosedBeech A23-24 · Penn Yan, NYFeb 12, 1972 · N7982L · Substantial damageNTSB MIA72DKW17Accident · Serious injuriesClosedPiper PA-28 · Dunlap, TNFeb 10, 1972 · N95122 · DestroyedNTSB NYC72FHH32Accident · No injuriesClosedTaylorcraft BC12-D · Souderton, PAFeb 6, 1972 · N43892 · Substantial damageNTSB MIA72FKG72Accident · Minor injuriesClosedStits SA3A · Stockbridge, GAFeb 5, 1972 · N75357 · Substantial damageNTSB FTW72DPD28Accident · No injuriesClosedCessna 150 · Archer City, TXFeb 4, 1972 · N8737S · Substantial damageNTSB MKC72FER38Accident · No injuriesClosedPiper PA-28R · Hopkins, MNFeb 3, 1972 · N7576J · Substantial damageNTSB OAK72DVM23Accident · No injuriesClosedCessna 182M · San Jose, CAFeb 2, 1972 · N70655 · Substantial damageNTSB NYC72FNE23Accident · No injuriesClosedRepublic RC-3 · Danbury, CTFeb 1, 1972 · N6111K · DestroyedNTSB CHI72FEC26Accident · No injuriesClosedCessna 150J · Ann Arbor, MIFeb 1, 1972 · N60897 · Substantial damageNTSB MKC72AK046Accident · Serious injuriesClosedPiper PA-15 · Great Bend, KSJan 30, 1972 · N4451H · 2 aircraft · DestroyedNTSB MIA72FLG24Accident · Minor injuriesClosedPiper PA-28 · Andrews, SCJan 30, 1972 · N8044W · Substantial damageNTSB LAX72FUA33Accident · No injuriesClosedGlobe GC-1B · S.J.Cpstrno, CAJan 30, 1972 · N3269K · Substantial damageNTSB DEN72FTE57Accident · No injuriesClosedAero Comdr 560-A · Durango, COJan 30, 1972 · N58D · Substantial damageNTSB NYC72DFA33Accident · No injuriesClosedLuscombe 8A · Massena, NYJan 29, 1972 · CF-H2B · Substantial damageNTSB SEA72FVC27Accident · Minor injuriesClosedBeech A23A · Portland, ORJan 28, 1972 · N3692Q · DestroyedNTSB LAX72FUJ69Accident · Minor injuriesClosedCessna 150 · Peoria, AZJan 28, 1972 · N8124S · DestroyedNTSB NYC72FHM27Accident · Minor injuriesClosedPiper J3F-65 · Wallkill, NYJan 26, 1972 · N42018 · Substantial damageNTSB NYC72FHD27Accident · No injuriesClosedAero Comdr 200-D · Vineland, NJJan 24, 1972 · N2957T · Substantial damageNTSB MKC72FTK21Accident · No injuriesClosedBeech A35 · Hot Springs, SDJan 23, 1972 · N8706A · Substantial damage