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 SEA68D0084Accident · No injuriesClosedWaco UMF · Mccall, IDAug 6, 1967 · N14076 · Substantial damageNTSB NYC68F0112Accident · Minor injuriesClosedTemco D-16A · Wayne, NJAug 6, 1967 · N122N · Substantial damageNTSB NYC68F0110Accident · Serious injuriesClosedPiper PA-24 · Nottingham, PAAug 6, 1967 · N8090P · DestroyedNTSB MKC68D0129Accident · No injuriesClosedPiper PA-28 · Wakonda, SDAug 6, 1967 · N6136W · Substantial damageNTSB ANC68D0052Accident · Minor injuriesClosedTaylorcraft BCS-65 · Annette, AKAug 6, 1967 · N34003 · Substantial damageNTSB MKC68D0220Accident · Minor injuriesClosedCessna 150 · Neosho, MOAug 5, 1967 · N8740S · DestroyedNTSB MKC68D0111Accident · No injuriesClosedCessna 170B · Garnavillo, IAAug 5, 1967 · N4628C · Substantial damageNTSB LAX68O0293Accident · No injuriesClosedPiper PA-24 · Alexis Creek, CanadaAug 5, 1967 · N6563P · Substantial damageNTSB FTW68F0227Accident · No injuriesClosedGrumman G-164 · Cotton Plant, ARAug 5, 1967 · N477Y · Substantial damageNTSB OAK68D0208Accident · No injuriesClosedBellanca 14-13 · Clarksburg, CAAug 3, 1967 · N86898 · Substantial damageNTSB MKC68F0126Accident · Serious injuriesClosedMooney M20D · Ames, IAAug 3, 1967 · N6631U · Substantial damageNTSB CHI68F0377Accident · Serious injuriesClosedVolmer VJ-22 · Eagle River, WIAug 3, 1967 · N7511U · DestroyedNTSB ANC68F0057Accident · No injuriesClosedStinson 108-2 · Homer, AKAug 2, 1967 · N480C · Substantial damageNTSB NYC68F0063Accident · No injuriesClosedCessna 150G · Collegeville, PAAug 1, 1967 · N8307J · Substantial damageNTSB UNK67X0144Accident · No injuriesClosedPiper PA-22 · T Hubert, CanadaJul 31, 1967 · N9734D · Substantial damageNTSB OAK68D0275Accident · No injuriesClosedPiper PA-25 · Cedarville, CAJul 31, 1967 · N4444Y · Substantial damageNTSB MKC68F0255Accident · No injuriesClosedPiper PA-28 · Rolla, MOJul 31, 1967 · N8884W · DestroyedNTSB SEA68D0134Accident · Minor injuriesClosedCessna 120 · Seattle, WAJul 30, 1967 · N77344 · DestroyedNTSB NYC68D0130Accident · No injuriesClosedGlobe GC-1B · Cherry Tree, PAJul 30, 1967 · N3215K · Substantial damageNTSB NYC68A0016Accident · Serious injuriesClosedHiller UH-12L · Allentown, PAJul 30, 1967 · N90458 · Substantial damageNTSB MIA68F0132Accident · Serious injuriesClosedCallair A-9B · Meigs, GAJul 30, 1967 · N7615V · Substantial damageNTSB FTW68A0010Accident · Serious injuriesClosedFerguson B8F · Dallas, TXJul 30, 1967 · N6511A · Substantial damageNTSB SEA68D0121Accident · Minor injuriesClosedPiper PA-28 · Swan Lake, MTJul 29, 1967 · N9456J · DestroyedNTSB CHI68F0121Accident · Minor injuriesClosedPiper PA-22 · Jackson, MIJul 29, 1967 · N9328D · Substantial damageNTSB OAK68A0007Accident · 1 fatalClosedVollmer FH-1 · Fremont, CAJul 28, 1967 · N4361 · DestroyedNTSB FTW68D0125Accident · No injuriesClosedPiper PA-25 · Lookeba, OKJul 28, 1967 · N6511Z · Substantial damageNTSB DEN68D0059Accident · No injuriesClosedFairchild WM62C · Clovis, NMJul 28, 1967 · N48706 · Substantial damageNTSB LAX68D0065Accident · No injuriesClosedNaval Fcty N3N · Chandler, AZJul 27, 1967 · N45151 · Substantial damageNTSB DIA68A0004Accident · Serious injuriesClosedCessna 140 · Baileys Crsrds, VAJul 27, 1967 · N76124 · Substantial damageNTSB ANC68A0005Accident · 3 fatalClosedBeech 35 · Anchorage, AKJul 27, 1967 · N3161V · DestroyedNTSB ANC68A0003Accident · Minor injuriesClosedMaule M-4 · Whittier, AKJul 27, 1967 · N9833M · Substantial damageNTSB MKC68F0112Accident · No injuriesClosedRepublic RC-3 · Nisswa, MNJul 26, 1967 · N6342K · Substantial damageNTSB LAX68A0014Accident · 3 fatalClosedBeech N35 · Los Angeles, CAJul 26, 1967 · N9434Y · DestroyedNTSB FTW68D0099Accident · No injuriesClosedBeech 23 · Weiner, ARJul 26, 1967 · N2392Z · Substantial damageNTSB CHI68D0178Accident · No injuriesClosedMooney M20C · Mt Carroll, ILJul 26, 1967 · N2751W · Substantial damageNTSB NYC68F0157Accident · Serious injuriesClosedGrumman G-44 · E Freetown, MAJul 25, 1967 · N9207H · Substantial damageNTSB CHI68F0219Accident · Minor injuriesClosedCessna 172 · Lexington, ILJul 24, 1967 · N3605L · Substantial damageNTSB NYC68F0080Accident · No injuriesClosedAeronca 7AC · Monroeville, PAJul 23, 1967 · N85148 · Substantial damageNTSB FTW68F0097Accident · No injuriesClosedCessna 150 · Dumas, ARJul 23, 1967 · N6217R · Substantial damageNTSB CHI68F0255Accident · No injuriesClosedPiper PA-28 · Alpena, MIJul 23, 1967 · N622W · Substantial damageNTSB CHI68F0212Accident · No injuriesClosedBeech A23 · Lexington, KYJul 23, 1967 · N2323W · Substantial damageNTSB MKC68F0122Accident · No injuriesClosedMooney M20C · Goodland, MNJul 22, 1967 · N6040Q · Substantial damageNTSB MKC68D0153Accident · No injuriesClosedBeech 95 · Kansas City, MOJul 22, 1967 · N150Y · Substantial damageNTSB OAK68D0165Accident · No injuriesClosedBoeing A75N1 · Dos Palos, CAJul 20, 1967 · N54671 · DestroyedNTSB LAX68A0010Accident · Serious injuriesClosedBell 47H-1 · San Brndino, CAJul 20, 1967 · N1211 · Substantial damageNTSB DEN68F0069Accident · No injuriesClosedAero Comdr 500 · Albuquerque, NMJul 20, 1967 · N7846C · Substantial damageNTSB MIA68A0008Accident · No injuriesClosedHughes 269A · Cape Canaverl, FLJul 19, 1967 · N8854F · Substantial damageNTSB CHI68A0011Accident · 2 fatalClosedChampion 7EC · Pewaukee, WIJul 19, 1967 · N8577E · DestroyedNTSB CHI68A0010Accident · 4 fatalClosedBeech A23 · Joliet, ILJul 19, 1967 · N8896M · DestroyedNTSB DEN68D0033Accident · No injuriesClosedTemco D-16 · Colo.Springs, COJul 18, 1967 · N2311B · Substantial damage