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 LAX67F0336Accident · Serious injuriesClosedPiper PA-28 · Flagstaff, AZMar 3, 1967 · N9131W · Substantial damageNTSB CHI67A0101Accident · No injuriesClosedBeech C-45H · Janesville, WIMar 3, 1967 · N9966Z · Substantial damageNTSB MKC67F0468Accident · Serious injuriesClosedCessna 150 · Kearney, MOMar 2, 1967 · N7842F · DestroyedNTSB DIA67A0011Accident · 2 fatalClosedBeech C-45H · Middletown, DEFeb 28, 1967 · N830K · DestroyedNTSB DEN67F0235Accident · Minor injuriesClosedCessna 150 · Salt Lake City, UTFeb 28, 1967 · N3757J · Substantial damageNTSB DEN67D0234Accident · No injuriesClosedChampion 7GCBC · Gillette, WYFeb 28, 1967 · N8363V · Substantial damageNTSB CHI67F0538Accident · No injuriesClosedBeech 35-A33 · Charlotte, MIFeb 28, 1967 · N421T · Substantial damageNTSB DEN67F0238Accident · No injuriesClosedBeech 95-B55 · Dubois, WYFeb 27, 1967 · N5731K · DestroyedNTSB CHI67F0503Accident · No injuriesClosedAero Comdr 680F · Columbus, OHFeb 27, 1967 · N6800S · Substantial damageNTSB NYC67F0517Accident · Minor injuriesClosedCessna 172G · Montauk, NYFeb 26, 1967 · N1335F · DestroyedNTSB MIA67A0089Accident · No injuriesClosedPiper PA-23 · St-Thomas, VIFeb 26, 1967 · N4308Y · DestroyedNTSB MKC67D0483Accident · No injuriesClosedCessna 140 · Cohasset, MNFeb 25, 1967 · N3709V · Substantial damageNTSB MIA67A0087Accident · 1 fatalClosedPiper PA-22 · La Follette, TNFeb 19, 1967 · N7758D · DestroyedNTSB DIA67F0049Accident · No injuriesClosedErcoupe 415-C · Laurel, MDFeb 19, 1967 · N99981 · Substantial damageNTSB CHI67F0529Accident · Minor injuriesClosedPiper PA-22 · Norwalk, OHFeb 18, 1967 · N8404D · Substantial damageNTSB CHI67A0098Accident · 2 fatalClosedCessna 182 · Youngstown, OHFeb 15, 1967 · N3796U · DestroyedNTSB MIA67F0396Accident · No injuriesClosedPiper PA-24 · Chattanooga, TNFeb 14, 1967 · N8562P · Substantial damageNTSB NYC67F0505Accident · No injuriesClosedMooney M20E · Albany, NYFeb 13, 1967 · N79817 · Substantial damageNTSB FTW67A0084Accident · 1 fatalClosedCessna 180 · Catahoula, LAFeb 13, 1967 · N53650 · DestroyedNTSB MIA67D0395Accident · No injuriesClosedPiper PA-24 · Savannah, TNFeb 12, 1967 · N5981P · Substantial damageNTSB NYC67D0569Accident · No injuriesClosedMaule M-4 · Norfolk, MAFeb 11, 1967 · N9810M · Substantial damageNTSB DIA67A0009Accident · 1 fatalClosedNaval Fcty N3N-3 · Hopewell, VAFeb 11, 1967 · N4743C · Substantial damageNTSB FTW67A0082Accident · 3 fatalClosedBeech D18S · Dallas, TXFeb 10, 1967 · N7388 · DestroyedNTSB CHI67F0479Accident · No injuriesClosedPiper PA-28 · Elgin, ILFeb 10, 1967 · N4011W · Substantial damageNTSB OAK67F0248Accident · No injuriesClosedErcoupe 415E · Carson City, NVFeb 9, 1967 · N94862 · Substantial damageNTSB DIA67F0036Accident · Minor injuriesClosedAlon A-2 · Chesapeake, VAFeb 8, 1967 · N6581Q · Substantial damageNTSB DEN67A0046Accident · 2 fatalClosedCessna 172C · Bluff, UTFeb 8, 1967 · N8449X · Substantial damageNTSB CHI67F0504Accident · No injuriesClosedPiper PA-24 · Medina, OHFeb 6, 1967 · N8052P · Substantial damageNTSB CHI67D0511Accident · No injuriesClosedCessna 310D · Mansfield, OHFeb 6, 1967 · N6826T · Substantial damageNTSB SEA67F0240Accident · No injuriesClosedLuscombe 8E · Colville, WAFeb 5, 1967 · CF-LYG · Substantial damageNTSB LAX67A0073Accident · No injuriesClosedHughes 269B · Culver City, CAFeb 4, 1967 · N9316F · Substantial damageNTSB NYC67D0518Accident · No injuriesClosedBeech A23 · Easton, PAJan 30, 1967 · N3550R · Substantial damageNTSB CHI67F0469Accident · No injuriesClosedCessna 182 · Rising Sun, INJan 29, 1967 · N8749T · Substantial damageNTSB CHI67F0466Accident · Minor injuriesClosedPiper PA-28 · Blue Ash, OHJan 29, 1967 · N5381W · DestroyedNTSB SEA67F0236Accident · Minor injuriesClosedPiper PA-28 · Mulino, ORJan 28, 1967 · N6727W · DestroyedNTSB LAX67A0070Accident · 1 fatalClosedBensen B-8M · Chino, CAJan 28, 1967 · N9677Z · DestroyedNTSB ANC68F0020Accident · Serious injuriesClosedPiper PA-20 · Kenai, AKJan 26, 1967 · N2245A · DestroyedNTSB DEN67D0194Accident · Minor injuriesClosedN.American AT-6 · Hurricane, UTJan 24, 1967 · N6128C · Substantial damageNTSB LAX67A0069Accident · 1 fatalClosedPiper PA-28 · S.Fernando, CAJan 23, 1967 · N136SF · Substantial damageNTSB CHI67F0585Accident · No injuriesClosedCessna 150G · Camp Grove, ILJan 23, 1967 · N3621J · Substantial damageNTSB NYC67F0475Accident · No injuriesClosedAeronca 65-C · Hanover, NJJan 22, 1967 · N4278A · Substantial damageNTSB MIA67F0406Accident · No injuriesClosedBeech B50 · Atlanta, GAJan 22, 1967 · N85TM · Substantial damageNTSB DIA67D0012Accident · No injuriesClosedPiper PA-22 · Randallstown, MDJan 18, 1967 · N3618Z · Substantial damageNTSB MKC67D0380Accident · No injuriesClosedPiper PA-28 · Iowa Falls, IAJan 17, 1967 · N6813W · Substantial damageNTSB ANC67A0053Accident · No injuriesClosedDehaviland DHC-3 · Anchorage, AKJan 17, 1967 · N98T · Substantial damageNTSB UNK67X0107Accident · Minor injuriesClosedPiper PA-18 · Narsuak, GreenlandJan 15, 1967 · N4176Z · DestroyedNTSB OAK67D0218Accident · No injuriesClosedBeech A35 · Visalia, CAJan 15, 1967 · N5428D · Substantial damageNTSB NYC67F0456Accident · Serious injuriesClosedCessna 182D · Hamburg, NYJan 14, 1967 · N8879X · Substantial damageNTSB DEN67D0206Accident · No injuriesClosedPiper PA-24 · Carrizozo, NMJan 14, 1967 · N7682P · Substantial damageNTSB OAK67F0221Accident · Minor injuriesClosedNavion NAV A · Tahoe Valley, CAJan 13, 1967 · N419C · Destroyed