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 DIA68F0017Accident · No injuriesClosedBoeing YL-15 · Westminster, MDJun 1, 1967 · N4210A · Substantial damageNTSB MIA67A0127Accident · No injuriesClosedHughes 269B · San Sebastian, PRMay 31, 1967 · N9395F · Substantial damageNTSB FTW68F0006Accident · No injuriesClosedBoeing E75N1 · Bay City, TXMay 31, 1967 · N1774B · Substantial damageNTSB FTW68D0036Accident · Serious injuriesClosedGrumman G-164 · Gueydan, LAMay 31, 1967 · N515Y · Substantial damageNTSB NYC68D0033Accident · No injuriesClosedStinson 108-3 · St.Regis Falls, NYMay 30, 1967 · N6759M · Substantial damageNTSB NYC67A0180Accident · No injuriesClosedPiper PA-28 · Warrington, PAMay 30, 1967 · N5399W · Substantial damageNTSB FTW67A0113Accident · 1 fatalClosedCessna 172F · Houma, LAMay 30, 1967 · N8806U · Substantial damageNTSB ANC67A0059Accident · No injuriesClosedHiller UH12EL · Tyonek, AKMay 30, 1967 · N5353V · Substantial damageNTSB OAK67D0334Accident · No injuriesClosedGrumman TBM · Carson City, NVMay 27, 1967 · N9433Z · Substantial damageNTSB OAK67A0084Accident · No injuriesClosedGrumman TBM-3E · Carson City, NVMay 27, 1967 · N9433Z · Substantial damageNTSB OAK67A0083Accident · 1 fatalClosedCessna 337 · Manchester, CAMay 27, 1967 · N5359S · DestroyedNTSB LAX67A0110Accident · 1 fatalClosedGrumman G-73 · Avalon, CAMay 27, 1967 · N2968 · DestroyedNTSB FTW68D0020Accident · No injuriesClosedBoeing A75N1 · Lake Charles, LAMay 27, 1967 · N1013M · Substantial damageNTSB FTW67F0659Accident · Serious injuriesClosedPiper PA-12 · Kilgore, TXMay 27, 1967 · N3040M · DestroyedNTSB DEN67A0073Accident · 2 fatalClosedPiper PA-24 · Raton, NMMay 27, 1967 · N8163P · DestroyedNTSB LAX67D0456Accident · Minor injuriesClosedCessna 150 · Santa Ana, CAMay 26, 1967 · N3205X · Substantial damageNTSB OAK67A0082Accident · No injuriesClosedBell 47G-2 · King City, CAMay 25, 1967 · N6728D · Substantial damageNTSB OAK67D0316Accident · No injuriesClosedGrumman G-164 · Live Oak, CAMay 24, 1967 · N657Y · Substantial damageNTSB OAK67A0081Accident · No injuriesClosedGrumman TBM-3E · Jiggs, NVMay 24, 1967 · N9651C · Substantial damageNTSB FTW67F0624Accident · No injuriesClosedCessna 150 · Addison, TXMay 24, 1967 · N2778S · Substantial damageNTSB CAN F0439Accident · No injuriesClosedBeech 35-33 · Scum Lake, CanadaMay 24, 1967 · N619V · Substantial damageNTSB SEA67D0348Accident · No injuriesClosedPiper PA-18 · Burley, IDMay 22, 1967 · N7668D · Substantial damageNTSB CAN F0438Accident · No injuriesClosedPiper PA-24 · Killaloe, CanadaMay 22, 1967 · N5465P · Substantial damageNTSB FTW67F0641Accident · No injuriesClosedPiper PA-24 · Kingfisher, OKMay 20, 1967 · N6212P · Substantial damageNTSB SEA68D0001Accident · No injuriesClosedCessna 210E · Missoula, MTMay 19, 1967 · N2339F · Substantial damageNTSB SEA67D0330Accident · No injuriesClosedPiper PA-25 · Palouse, WAMay 19, 1967 · N6807Z · Substantial damageNTSB FTW67D0597Accident · No injuriesClosedPiper PA-32 · Baton Rouge, LAMay 19, 1967 · N3591W · Substantial damageNTSB DEN67F0269Accident · No injuriesClosedBeech 23 · Grants, NMMay 19, 1967 · N2376Q · Substantial damageNTSB NYC68F0005Accident · No injuriesClosedAeronca S15AC · Parishville, NYMay 18, 1967 · N1088H · Substantial damageNTSB MIA67A0122Accident · 1 fatalClosedCessna 140 · Miami, FLMay 18, 1967 · N2725N · DestroyedNTSB FTW67F0618Accident · No injuriesClosedBeech P35 · El Paso, TXMay 18, 1967 · N9675Y · Substantial damageNTSB FTW67D0616Accident · No injuriesClosedPiper PA-20 · Athens, LAMay 18, 1967 · N7057K · Substantial damageNTSB DEN67A0070Accident · No injuriesClosedKaman H43-A · Lander, WYMay 17, 1967 · N2856J · Substantial damageNTSB NYC67F0665Accident · Serious injuriesClosedPiper J3-L65 · Rhinebeck, NYMay 14, 1967 · N38386 · DestroyedNTSB LAX67F0402Accident · Serious injuriesClosedLuscombe 8A · Big Bear, CAMay 14, 1967 · N41925 · DestroyedNTSB DEN67F0285Accident · No injuriesClosedMooney M20D · Aguna, NMMay 14, 1967 · N6627U · Substantial damageNTSB CHI67F0622Accident · Minor injuriesClosedErcoupe 415CD · Wyoming, ILMay 14, 1967 · N3866H · Substantial damageNTSB NYC67F0649Accident · No injuriesClosedPiper J-3 · Catskill, NYMay 13, 1967 · N3502N · DestroyedNTSB MIA67A0120Accident · Serious injuriesClosedHughes 269B · Saluda, SCMay 13, 1967 · N9417F · Substantial damageNTSB FTW67A0108Accident · 1 fatalClosedCessna 180 · Grand Chenier, LAMay 13, 1967 · N4745U · DestroyedNTSB CHI67D0654Accident · No injuriesClosedBeech A23 · Polo, ILMay 13, 1967 · N8773M · Substantial damageNTSB DIA67F0106Accident · Serious injuriesClosedCessna 150 · Chapel Hill, NCMay 10, 1967 · N8304G · DestroyedNTSB CHI68F0033Accident · No injuriesClosedCessna 210 · Elyria, OHMay 10, 1967 · N9504T · Substantial damageNTSB CHI67D0648Accident · No injuriesClosedCessna 172 · Plainville, ILMay 9, 1967 · N2181Y · Substantial damageNTSB OAK67D0324Accident · No injuriesClosedStinson L-5 · Dos Palos, CAMay 8, 1967 · N63108 · Substantial damageNTSB FTW67D0634Accident · No injuriesClosedCessna 172 · Augusta, ARMay 8, 1967 · N8316B · Substantial damageNTSB MKC67D0601Accident · No injuriesClosedAeronca 7EC · Ranier, MNMay 7, 1967 · N4370C · Substantial damageNTSB MKC67D0518Accident · No injuriesClosedPiper PA-22 · Van Meter, IAMay 7, 1967 · N9039D · Substantial damageNTSB LAX67A0104Accident · 2 fatalClosedBeech S35 · Nipton, CAMay 7, 1967 · N5791K · DestroyedNTSB ANC68F0004Accident · No injuriesClosedChampion 7ECA · Cordova, AKMay 7, 1967 · N2517F · Substantial damage