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 MKC72AK060Accident · 1 fatalClosedPiper J3C-65 · St.Joseph, MOMay 16, 1972 · N9095C · DestroyedNTSB MIA72FKG99Accident · No injuriesClosedPiper PA-23 · Chamblee, GAMay 16, 1972 · N5220Y · Substantial damageNTSB FTW72FPG75Accident · Minor injuriesClosedGrumman G-164 · Stuttgart, ARMay 16, 1972 · N642U · Substantial damageNTSB SEA72FYA33Accident · Serious injuriesClosedPiper PA-25 · Oakley, IDMay 15, 1972 · N8707L · DestroyedNTSB OAK72FVG57Accident · Minor injuriesClosedPiper PA-28 · Montague, CAMay 15, 1972 · N8619W · Substantial damageNTSB OAK72DVG60Accident · No injuriesClosedPiper PA-28 · Ruth, CAMay 14, 1972 · N5171S · Substantial damageNTSB NYC72FHM49Accident · No injuriesClosedMccullough J-2 · Hillburn, NYMay 14, 1972 · N4323G · Substantial damageNTSB OAK72FXQ32Accident · No injuriesClosedCessna 210 · Winnemucca, NVMay 13, 1972 · N6513F · Substantial damageNTSB NYC72FFH42Accident · Minor injuriesClosedPiper J-3 · New Salem, NYMay 13, 1972 · N19AN · Substantial damageNTSB SEA72FTI18Accident · No injuriesClosedPiper PA-30 · Thmpson Flls, MTMay 12, 1972 · N7788Y · Substantial damageNTSB DEN72FTM16Accident · Minor injuriesClosedCessna 182A · Logan, UTMay 11, 1972 · N6095B · Substantial damageNTSB CHI72FEV31Accident · Serious injuriesClosedBensen B8M · Bloomington, ILMay 10, 1972 · N30082 · Minor damageNTSB CHI72FEA41Accident · No injuriesClosedPiper PA-22 · Muncie, INMay 9, 1972 · N7220D · Substantial damageNTSB OAK72FVD41Accident · 1 fatalClosedGen Dynamic L-13A · Lodi, CAMay 8, 1972 · N68844 · DestroyedNTSB FTW72FRA54Accident · Minor injuriesClosedBoeing IB75A · Winnie, TXMay 8, 1972 · N55099 · DestroyedNTSB MIA72FKW26Accident · Serious injuriesClosedPiper PA-32 · Smithville, TNMay 7, 1972 · N4842S · DestroyedNTSB NYC72FFA37Accident · No injuriesClosedCessna 172G · New Brennan, NYMay 4, 1972 · N1331F · Substantial damageNTSB FTW72FRD24Accident · No injuriesClosedBoeing A75N1 · Rayne, LAMay 4, 1972 · N59364 · Substantial damageNTSB DEN72FQA39Accident · No injuriesClosedPiper PA-24 · Alameda, NMMay 4, 1972 · N6359P · Substantial damageNTSB MKC72FCJ59Accident · No injuriesClosedPiper PA-34 · Omaha, NEMay 2, 1972 · N1060U · Substantial damageNTSB NYC72FNC42Accident · Serious injuriesClosedStinson 108-3 · Princeton, MEApr 30, 1972 · N6070M · Substantial damageNTSB FTW72DRG79Accident · Serious injuriesClosedPiper PA-18 · Pleasanton, TXApr 30, 1972 · N9266D · Substantial damageNTSB FTW72AF073Accident · 1 fatalClosedAeronca O-58B · Sulphur Sprgs, TXApr 30, 1972 · N47883 · DestroyedNTSB CHI72FEM61Accident · Serious injuriesClosedBeech C23 · Lansing, MIApr 30, 1972 · N8063R · DestroyedNTSB OAK72FVW29Accident · No injuriesClosedCessna 172G · Las Vegas, NVApr 29, 1972 · N4294L · Substantial damageNTSB OAK72DVG52Accident · No injuriesClosedBeech 35 · Fair Oaks, CAApr 29, 1972 · N3908N · Substantial damageNTSB NYC72FNA38Accident · No injuriesClosedPiper PA-28R · Plymouth, MAApr 29, 1972 · N7530J · Substantial damageNTSB NYC72FID25Accident · Minor injuriesClosedCessna 150G · Chesterfield, VAApr 29, 1972 · N2604J · DestroyedNTSB DEN72DTE68Accident · Minor injuriesClosedPiper PA-28R · Alamosa, COApr 29, 1972 · N9371N · Substantial damageNTSB FTW72DPJ38Accident · No injuriesClosedPiper PA-25 · Lindsay, OKApr 28, 1972 · N7386Z · Substantial damageNTSB CHI72FER03Accident · No injuriesClosedBeech 35-C33 · Star Prairie, WIApr 27, 1972 · N3724Q · Substantial damageNTSB FTW72FPG70Accident · No injuriesClosedCessna 172 · Ashdown, ARApr 24, 1972 · N4116L · Substantial damageNTSB NYC72FFJ39Accident · Serious injuriesClosedEaa Biplane P · Batavia, NYApr 23, 1972 · N4941 · Substantial damageNTSB LAX72DVA68Accident · Minor injuriesClosedCessna 172 · Olancha, CAApr 23, 1972 · N3832S · Substantial damageNTSB NYC72FNE34Accident · Minor injuriesClosedAeronca 11AC · Easton, CTApr 21, 1972 · N85810 · Substantial damageNTSB MIA72AM112Accident · No injuriesClosedDouglas DC-8 · San Juan, PRApr 21, 1972 · N8776 · No damageNTSB LAX72AL053Accident · 2 fatalClosedPiper PA-31P · San Diego, CAApr 21, 1972 · N6815L · DestroyedNTSB SEA72DYA30Accident · Minor injuriesClosedBeech C-18S · Blackfoot, IDApr 20, 1972 · N4289A · Substantial damageNTSB NYC72FKM33Accident · No injuriesClosedPiper PA-22 · Wadesboro, NCApr 20, 1972 · N7129B · Substantial damageNTSB CHI72FET23Accident · No injuriesClosedCessna 120 · Watervliet, MIApr 19, 1972 · N277N · Substantial damageNTSB CHI72FEM65Accident · No injuriesClosedGrumman G-164 · Sparta, MIApr 19, 1972 · N697Y · Substantial damageNTSB ANC72AA042Accident · No injuriesClosedPilatus PC-6 · Deadhorse, AKApr 19, 1972 · N3602R · Substantial damageNTSB SEA72FYI19Accident · Serious injuriesClosedErco 415-C · Bonanza, ORApr 18, 1972 · N99237 · Substantial damageNTSB NYC72IN132Incident · No injuriesClosedDouglas DC-8 · Newark, NJApr 18, 1972 · N787FT · Minor damageNTSB NYC72DIJ40Accident · No injuriesClosedPiper PA-18 · Hamlin, WVApr 18, 1972 · N3842Z · Substantial damageNTSB MIA72FKJ50Accident · No injuriesClosedPiper PA-22 · Lacey Springs, ALApr 18, 1972 · N3231B · DestroyedNTSB FTW72FRJ24Accident · No injuriesClosedAeronca 7AC · Atlanta, TXApr 18, 1972 · N84303 · Substantial damageNTSB LAX72FUQ79Accident · No injuriesClosedPiper PA-28 · Quartz Hill, CAApr 17, 1972 · N5541F · Substantial damageNTSB NYC72FID26Accident · Serious injuriesClosedPiper J3C-65 · Bedford, VAApr 16, 1972 · N6878H · Substantial damageNTSB MKC72FCJ58Accident · Serious injuriesClosedStinson 108-2 · Seward, NEApr 16, 1972 · N8047K · Destroyed