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 LAX67D0352Accident · No injuriesClosedCessna 210 · Compton, CAApr 9, 1967 · N7338E · Substantial damageNTSB FTW67A0098Accident · 1 fatalClosedCessna 172 · Lakeview, ARApr 9, 1967 · N5945A · Substantial damageNTSB NYC67I0136Incident · No injuriesClosedDouglas DC-8 · Jamaica, NYApr 8, 1967 · N8612 · No damageNTSB FTW67F0534Accident · No injuriesClosedCessna 188 · Rosenberg, TXApr 8, 1967 · N5541S · Substantial damageNTSB DEN67D0259Accident · No injuriesClosedPiper PA-18 · Arvada, WYApr 8, 1967 · N7573K · Substantial damageNTSB CHI67A0114Accident · No injuriesClosedNord-Avion 262-A · Chicago, ILApr 8, 1967 · N26210 · Substantial damageNTSB MKC67D0539Accident · No injuriesClosedPiper PA-22 · Kirksville, MOApr 7, 1967 · N68FF · Substantial damageNTSB CHI67A0112Accident · 9 fatalClosedBeech C-45H · Lexington, KYApr 3, 1967 · N3727G · DestroyedNTSB SEA67A0055Accident · 1 fatalClosedBellanca 14-13 · Vashon, WAApr 2, 1967 · N86747 · DestroyedNTSB FTW67D0531Accident · Minor injuriesClosedBoeing E75 · Marianna, ARApr 2, 1967 · N1383V · Substantial damageNTSB NYC67F0611Accident · Minor injuriesClosedBoeing B75 · Callicoon, NYApr 1, 1967 · N64590 · Substantial damageNTSB CHI67F0630Accident · No injuriesClosedCessna 180 · Manistique, MIApr 1, 1967 · N9279C · Substantial damageNTSB MIA67A0102Accident · Minor injuriesClosedBell 47J-2 · Starke, FLMar 31, 1967 · N73258 · Substantial damageNTSB OAK67D0281Accident · No injuriesClosedBoeing 75N1 · Mckittrick, CAMar 30, 1967 · N56944 · DestroyedNTSB CHI67A0111Accident · 2 fatalClosedStinson 108-2 · Chandlerville, ILMar 30, 1967 · N9302K · DestroyedNTSB NYC67F0590Accident · No injuriesClosedPiper PA-22 · Henrietta, NYMar 27, 1967 · N5920Z · Substantial damageNTSB DIA67F0076Accident · No injuriesClosedCessna 150 · Lorton, VAMar 27, 1967 · N8785S · Substantial damageNTSB NYC67F0572Accident · Minor injuriesClosedCessna 150 · Mt Pleasant, PAMar 26, 1967 · N1952Z · Substantial damageNTSB MIA67A0097Accident · 2 fatalClosedAeronca 7AC · Homestead, FLMar 26, 1967 · N81839 · Substantial damageNTSB CHI67A0109Accident · 1 fatalClosedPiper PA-22 · Mishawaka, INMar 26, 1967 · N8688C · DestroyedNTSB MIA67F0461Accident · Minor injuriesClosedCessna 172 · Collegedale, TNMar 25, 1967 · N9122B · DestroyedNTSB DEN67A0055Accident · 1 fatalClosedWaco UPF-7 · Las Cruces, NMMar 25, 1967 · N32094 · DestroyedNTSB SEA67F0285Accident · Serious injuriesClosedPiper J5A · Pt.Townsend, WAMar 23, 1967 · N32896 · Substantial damageNTSB FTW67F0577Accident · No injuriesClosedBoeing A75 · Marianna, ARMar 23, 1967 · N55065 · Substantial damageNTSB CHI68F0106Accident · Serious injuriesClosedBeech 95-C55 · Chicago, ILMar 22, 1967 · N7981M · Substantial damageNTSB NYC67F0568Accident · No injuriesClosedCessna 150 · Simsbury, CTMar 20, 1967 · N8317J · Substantial damageNTSB SEA67A0052Accident · No injuriesClosedHughes 269A · Yakima, WAMar 19, 1967 · N8888F · Substantial damageNTSB MKC67D0488Accident · No injuriesClosedCessna 120 · Clayton, KSMar 19, 1967 · N1731N · Substantial damageNTSB MIA67A0107Accident · No injuriesClosedBeech C-45G · Arecibo, PRMar 19, 1967 · N9470Z · DestroyedNTSB CHI67F0606Accident · Serious injuriesClosedPiper PA-23 · Alsip, ILMar 17, 1967 · N1485P · DestroyedNTSB ANC67A0055Accident · 2 fatalClosedBeech D18S · Kodiak, AKMar 16, 1967 · N80194 · DestroyedNTSB DEN67D0227Accident · No injuriesClosedCessna 172G · Montrose, CAMar 14, 1967 · N3884L · Substantial damageNTSB DEN67F0231Accident · No injuriesClosedBeech A23A · Albuquerque, NMMar 13, 1967 · N4701J · Substantial damageNTSB MIA67F0422Accident · Minor injuriesClosedCessna 172 · Opa Locka, FLMar 12, 1967 · N3940L · Substantial damageNTSB FTW67F0498Accident · Serious injuriesClosedCessna 140 · San Marcos, TXMar 12, 1967 · N76851 · Substantial damageNTSB DIA67D0056Accident · No injuriesClosedCommonwlth 185 · Boone, NCMar 12, 1967 · N34130 · Substantial damageNTSB SEA67D0258Accident · Minor injuriesClosedMooney M20B · North Powder, ORMar 11, 1967 · N78910 · Substantial damageNTSB MKC67F0534Accident · No injuriesClosedBeech 95-C55 · Camdenton, MOMar 11, 1967 · N7926M · Substantial damageNTSB MKC67F0457Accident · No injuriesClosedBeech A23 · Cedar Rapids, IAMar 10, 1967 · N5959S · Substantial damageNTSB LAX67F0333Accident · 1 fatalClosedCessna 172 · Phoenix, AZMar 9, 1967 · N3959S · Substantial damageNTSB DEN67A0051Accident · 2 fatalClosedBeech A23 · Albuquerque, NMMar 9, 1967 · N4715J · DestroyedNTSB OAK67F0250Accident · No injuriesClosedCessna 320 · Reno, NVMar 7, 1967 · N5799X · Substantial damageNTSB CHI67F0505Accident · No injuriesClosedBeech 95-B55 · Waukesha, WIMar 6, 1967 · N2030W · Substantial damageNTSB OAK67F0301Accident · No injuriesClosedBeech B35 · Carson City, NVMar 5, 1967 · N8858A · Substantial damageNTSB MIA67F0452Accident · No injuriesClosedCessna 140 · Amory, MSMar 5, 1967 · N2194N · Substantial damageNTSB LAX67D0359Accident · No injuriesClosedCessna 182 · Lancaster, CAMar 5, 1967 · N2824F · Substantial damageNTSB LAX67D0330Accident · Minor injuriesClosedNavy N3N · Mesa, AZMar 5, 1967 · N44981 · Substantial damageNTSB FTW67A0089Accident · 2 fatalClosedTaylorcraft BC12-D · Pleasanton, TXMar 5, 1967 · N43090 · DestroyedNTSB NYC67D0520Accident · No injuriesClosedHelio Acft H-250 · Hacketstown, NJMar 4, 1967 · N5457E · Substantial damageNTSB DIA67F0053Accident · No injuriesClosedBeech A35 · Smithfield, NCMar 4, 1967 · N513B · Destroyed