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 FTW67F0312Accident · No injuriesClosedCallair A-4 · Dinburg, TXNov 10, 1966 · N6150C · Substantial damageNTSB FTW67A0050Accident · Serious injuriesClosedHughes 269B · New Orleans, LANov 9, 1966 · N9334F · DestroyedNTSB MKC67D0295Accident · No injuriesClosedDehaviland DHC-2 · Forsyth, MONov 8, 1966 · N192Z · Substantial damageNTSB MKC67A0034Accident · No injuriesClosedBell 47G · Hariton, IANov 7, 1966 · N4734S · Substantial damageNTSB CHI67F0332Accident · Minor injuriesClosedPietenpol Camper · Plymouth, MINov 7, 1966 · N4062C · Substantial damageNTSB MKC67F0336Accident · Serious injuriesClosedPiper PA-24 · Rolla, MONov 6, 1966 · N8678P · DestroyedNTSB MKC67F0310Accident · No injuriesClosedRyan PT-22 · Isle, MNNov 6, 1966 · N47843 · Substantial damageNTSB LAX67D0202Accident · Minor injuriesClosedPiper PA-11 · Buckeye, AZNov 6, 1966 · N5086H · Substantial damageNTSB CHI67F0391Accident · No injuriesClosedCessna 310D · Griffith, INNov 5, 1966 · N664R · Substantial damageNTSB SEA67D0178Accident · Minor injuriesClosedCessna 150 · Wayan, IDNov 2, 1966 · N4800X · Substantial damageNTSB SEA67D0192Accident · No injuriesClosedBeech 35 · Thom Falls, MTOct 29, 1966 · N80415 · Substantial damageNTSB SEA67D0164Accident · Minor injuriesClosedAeronca 7AC · Twin Falls, IDOct 29, 1966 · N3085E · Substantial damageNTSB NYC67F0355Accident · No injuriesClosedBeechcraft E18S · Reedsville, PAOct 27, 1966 · N219Y · Substantial damageNTSB CHI67F0297Accident · No injuriesClosedLuscombe 8A · Gaines, MIOct 27, 1966 · N1429K · DestroyedNTSB FTW67F0281Accident · Minor injuriesClosedPiper PA-24 · Mineral Wells, TXOct 26, 1966 · N8317P · Substantial damageNTSB DCA67I0008Incident · No injuriesClosedLockheed 1049G · Andrews Afb, MDOct 26, 1966 · N6204G · No damageNTSB MKC67F0274Accident · No injuriesClosedCessna 120 · Rosemount, MNOct 25, 1966 · N77358 · Substantial damageNTSB CHI67D0408Accident · No injuriesClosedMooney M20C · Pikeville, KYOct 25, 1966 · N6064Q · Substantial damageNTSB MKC67D0253Accident · No injuriesClosedMooney M20C · Dexter, KSOct 24, 1966 · N5543Q · Substantial damageNTSB CHI67F0281Accident · Serious injuriesClosedCessna U206 · Columbus, OHOct 24, 1966 · N377LA · DestroyedNTSB NYC67F0344Accident · Serious injuriesClosedPiper PA-12 · Waynesboro, VAOct 23, 1966 · N2890M · Substantial damageNTSB MKC67F0258Accident · Serious injuriesClosedPiper PA-24 · Kingman, KSOct 23, 1966 · N5723P · Substantial damageNTSB MIA67F0244Accident · No injuriesClosedGlobe GC-1B · Brooksville, FLOct 23, 1966 · N80682 · Substantial damageNTSB FTW67D0308Accident · No injuriesClosedBeech S35 · Little Rock, AROct 23, 1966 · N5806K · Substantial damageNTSB CHI67A0054Accident · Serious injuriesClosedBensen B7MC · French Lick, INOct 23, 1966 · N12059 · Substantial damageNTSB SEA67D0196Accident · No injuriesClosedPiper PA-22 · Mulino, OROct 22, 1966 · N623A · Substantial damageNTSB NYC67A0063Accident · 1 fatalClosedSchweizer SGS232 · Morrisville, VTOct 22, 1966 · N5841V · DestroyedNTSB MKC67F0300Accident · No injuriesClosedBeech 23 · Burden, KSOct 22, 1966 · N2302Z · Substantial damageNTSB LAX67D0184Accident · No injuriesClosedErcoupe 415-C · Agua Dulce, CAOct 22, 1966 · N2921H · Substantial damageNTSB FTW67F0293Accident · No injuriesClosedCessna 150 · Blanco, TXOct 22, 1966 · N716LC · Substantial damageNTSB CHI67A0053Accident · 2 fatalClosedPiper PA-22 · Stow, OHOct 21, 1966 · N6952D · DestroyedNTSB NYC67F0308Accident · No injuriesClosedHelio H-391B · Manville, NJOct 20, 1966 · N6021 · Substantial damageNTSB MKC67F0267Accident · Minor injuriesClosedCessna 206 · Clear Lake, IAOct 19, 1966 · N4644F · Substantial damageNTSB LAX67A0042Accident · No injuriesClosedLockheed 18 · Pacoima, CAOct 19, 1966 · N699C · Substantial damageNTSB MIA67F0257Accident · No injuriesClosedPiper PA-28 · Miami, FLOct 18, 1966 · N7599R · Substantial damageNTSB NYC67F0315Accident · No injuriesClosedCessna 210E · Newburg, MDOct 16, 1966 · N4974U · Substantial damageNTSB MKC67D0279Accident · No injuriesClosedCessna 170 · Thedford, NEOct 16, 1966 · N2551V · Substantial damageNTSB MKC67D0255Accident · No injuriesClosedPiper PA-22 · De Witt, IAOct 16, 1966 · N4744A · Substantial damageNTSB DEN67D0124Accident · No injuriesClosedPiper PA-24 · Willard, NMOct 15, 1966 · N8581P · Substantial damageNTSB UNK67X0102Accident · Serious injuriesClosedGrumman G-44 · Ft Wm Ont., CanadaOct 14, 1966 · N1173V · DestroyedNTSB NYC67A0060Accident · 1 fatalClosedLockheed L-18 · Short Creek, WVOct 13, 1966 · N131E · DestroyedNTSB CHI67F0290Accident · Serious injuriesClosedMeyers 200-B · East Jordan, MIOct 13, 1966 · N34394 · Substantial damageNTSB CHI67D0293Accident · No injuriesClosedNavion A · Stevens Point, WIOct 12, 1966 · N4002K · Substantial damageNTSB OAK67A0043Accident · No injuriesClosedSikorsky S-61N · Mountain View, CAOct 11, 1966 · N4606G · Substantial damageNTSB FTW67F0245Accident · Serious injuriesClosedPiper PA-25 · Eudora, AROct 11, 1966 · N6804Z · DestroyedNTSB CAN66F0424Accident · 2 fatalClosedCessna 180 · Rutter Lk, CanadaOct 11, 1966 · N2280C · DestroyedNTSB MIA67F0215Accident · No injuriesClosedSnow S2C · Independence, MSOct 10, 1966 · N9488R · DestroyedNTSB SEA67D0168Accident · No injuriesClosedMooney M20B · W S Springs, MTOct 9, 1966 · N74785 · Substantial damageNTSB OAK67A0042Accident · 1 fatalClosedCessna 150F · Bass Lake, CAOct 9, 1966 · N7847F · Substantial damageNTSB MIA67F0285Accident · No injuriesClosedPiper PA-18A · Jay, FLOct 8, 1966 · N1493C · Substantial damage