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 ANC72DAG06Accident · No injuriesClosedCessna 180H · Alakanuk, AKJul 2, 1971 · N9077M · Substantial damageNTSB SEA72DYC01Accident · Minor injuriesClosedCessna A185E · John Day, ORJul 1, 1971 · N4740Q · DestroyedNTSB MKC72FCJ01Accident · Minor injuriesClosedGrumman G-164 · Ainsworth, NEJul 1, 1971 · N730Y · DestroyedNTSB MKC72DER01Accident · No injuriesClosedPiper J3C · Rapidan, MNJul 1, 1971 · N3675K · Substantial damageNTSB LAX72FUJ08Accident · No injuriesClosedCessna 177 · Glendale, AZJul 1, 1971 · N2292Y · Substantial damageNTSB FTW72OFO70Accident · Minor injuriesClosedBell 205A-1 · Costa Rica, SerbiaJul 1, 1971 · N4771R · DestroyedNTSB FTW72FPD01Accident · Serious injuriesClosedTaylorcraft BC12-D · Southlake, TXJul 1, 1971 · N95948 · Substantial damageNTSB CHI72FEV01Accident · Serious injuriesClosedChampion 7AC · Breese, ILJul 1, 1971 · N2515E · DestroyedNTSB SEA71DYC47Accident · No injuriesClosedCessna 172F · Lostine, ORJun 30, 1971 · N8303U · DestroyedNTSB MKC71FCA42Accident · No injuriesClosedPiper PA-12 · Dexter, MOJun 29, 1971 · N7626H · Substantial damageNTSB FTW71DQD28Accident · No injuriesClosedCessna 182 · Sierra Blanca, TXJun 29, 1971 · N3158F · Substantial damageNTSB CHI71IC094Incident · No injuriesClosedCessna 414 · Detroit, MIJun 28, 1971 · N414GW · Minor damageNTSB MKC71FER77Accident · Minor injuriesClosedCommonwlth 8135 · Royalton, MNJun 27, 1971 · N25552 · Substantial damageNTSB IAD71DIJ24Accident · No injuriesClosedTaylorcraft BC12C · Frankford, WVJun 27, 1971 · N43909 · Substantial damageNTSB FTW72DPG01Accident · No injuriesClosedCessna 172 · Rogers, ARJun 27, 1971 · N6072R · Substantial damageNTSB CHI71FEK41Accident · Minor injuriesClosedCessna 150G · Columbus, OHJun 27, 1971 · N3683J · Substantial damageNTSB SEA71DYA41Accident · Minor injuriesClosedPiper J-3 · Paris, IDJun 26, 1971 · N38631 · Substantial damageNTSB MKC71FTG35Accident · Minor injuriesClosedCessna 172G · Alexander, NDJun 26, 1971 · N3844L · Substantial damageNTSB FTW71AF086Accident · No injuriesClosedCessna 180H · Palmetto, LAJun 26, 1971 · N8236V · Substantial damageNTSB MIA71FKT67Accident · Minor injuriesClosedBeech C45-H · Dania, FLJun 25, 1971 · N355D · DestroyedNTSB MKC71FTG32Accident · No injuriesClosedPiper PA-18 · Rolla, NDJun 22, 1971 · N4110Z · Substantial damageNTSB MIA71AM115Accident · 2 fatalClosedBeech E18S · Birmingham, ALJun 22, 1971 · N5605D · DestroyedNTSB LAX71FUG95Accident · No injuriesClosedCessna 150 · Corona, CAJun 22, 1971 · N3394J · Substantial damageNTSB FTW71DPM32Accident · No injuriesClosedGrumman G-164 · Dustin, OKJun 22, 1971 · N582Y · Substantial damageNTSB MKC71FTK32Accident · No injuriesClosedGlobe GC-1B · Rapid City, SDJun 21, 1971 · N3366K · Substantial damageNTSB DEN71DQA59Accident · No injuriesClosedBeech 35 · Eagle West, NMJun 20, 1971 · N1166 · Substantial damageNTSB CHI71FEC57Accident · Minor injuriesClosedPiper PA-24 · Howell, MIJun 20, 1971 · N7491P · Substantial damageNTSB OAK71FXQ44Accident · Minor injuriesClosedCessna 177 · Minden, NVJun 19, 1971 · N29636 · Substantial damageNTSB OAK71FVM56Accident · Serious injuriesClosedBeech 35 · San Carlos, CAJun 19, 1971 · N3934N · Substantial damageNTSB NYC71FHM32Accident · No injuriesClosedStinson 108 · Washingtonvlle, NYJun 19, 1971 · N97320 · Substantial damageNTSB LAX71FVA90Accident · Serious injuriesClosedBoeing 75 · Buttonwillw, CAJun 19, 1971 · N56796 · DestroyedNTSB CHI71FEP54Accident · No injuriesClosedPietenpol GN-1 · Menomonee Fls, WIJun 19, 1971 · N4705G · Substantial damageNTSB CHI71DEM88Accident · Minor injuriesClosedCessna 177 · Boyne Falls, MIJun 19, 1971 · N3276T · Substantial damageNTSB OAK71DVG85Accident · No injuriesClosedBeech B35 · Rio Linda, CAJun 18, 1971 · N5062C · Substantial damageNTSB MKC71FCD89Accident · No injuriesClosedBensen B-8M · Ames, IAJun 18, 1971 · N6519 · Substantial damageNTSB FTW71FPJ46Accident · 1 fatalClosedPiper PA-25 · Duncan, OKJun 18, 1971 · N6983Z · DestroyedNTSB CHI71AC090Accident · 2 fatalClosedCessna 320C · Howell, MIJun 18, 1971 · N3036T · DestroyedNTSB DEN71FQD26Accident · 1 fatalClosedBell 47J · Deming, NMJun 17, 1971 · N6743D · DestroyedNTSB MKC71DCQ43Accident · No injuriesClosedCessna 180C · Syracuse, KSJun 16, 1971 · N9366T · Substantial damageNTSB FTW71DPG64Accident · No injuriesClosedPiper PA-25 · Tuckerman, ARJun 16, 1971 · N4552Y · Substantial damageNTSB LAX71FUG92Accident · No injuriesClosedFairchild M62A · Upland, CAJun 15, 1971 · N54140 · DestroyedNTSB SEA71FYA35Accident · Serious injuriesClosedPiper PA-28 · Boise, IDJun 13, 1971 · N6786W · Substantial damageNTSB MKC71FER72Accident · Serious injuriesClosedCessna 150 · Browerville, MNJun 13, 1971 · N6922S · Substantial damageNTSB FTW71FRA67Accident · Minor injuriesClosedCessna 150L · China, TXJun 13, 1971 · N6700G · Substantial damageNTSB DEN71AD048Accident · 3 fatalClosedBeech 23 · Rocky Ford, COJun 13, 1971 · N6993Q · DestroyedNTSB CHI71FLQ25Accident · No injuriesClosedCessna 150K · New Haven, KYJun 12, 1971 · N6045G · Substantial damageNTSB CHI71AC088Accident · 3 fatalClosedCessna 180 · Whitmore Lk, MIJun 12, 1971 · N9356T · DestroyedNTSB FTW71IF082Incident · No injuriesClosedDouglas DC-3A · Okla.City, OKJun 11, 1971 · N692A · Minor damageNTSB CHI71FLQ27Accident · Minor injuriesClosedNavion 4 · Berea, KYJun 10, 1971 · N91630 · DestroyedNTSB MIA71DKJ59Accident · No injuriesClosedCessna 150 · Muscle Shoals, ALJun 9, 1971 · N2968S · Substantial damage