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 SEA68A0007Accident · No injuriesClosedBell 47G-2 · Palisades, WAJul 17, 1967 · N3794G · Substantial damageNTSB NYC68F0302Accident · No injuriesClosedPiper PA-22 · Newburyport, MAJul 16, 1967 · N7639D · Substantial damageNTSB LAX68F0087Accident · 1 fatalClosedPiper PA-25 · Maricopa, AZJul 16, 1967 · N7434Z · DestroyedNTSB FTW68D0253Accident · Minor injuriesClosedCessna 175 · Merit, TXJul 16, 1967 · N7030E · Substantial damageNTSB DIA68F0057Accident · No injuriesClosedPiper J3L-65 · Fallston, MDJul 16, 1967 · N38118 · DestroyedNTSB MKC68F0266Accident · Serious injuriesClosedAeronca 11AC · Lagrange, MOJul 15, 1967 · N86393 · DestroyedNTSB DIA68D0043Accident · Minor injuriesClosedGoodyear FG-1D · Norfolk, VAJul 15, 1967 · N448AG · Substantial damageNTSB CHI68F0125Accident · Minor injuriesClosedPiper PA-28 · Three Lakes, WIJul 15, 1967 · N8956W · Substantial damageNTSB SEA69F0125Accident · 1 fatalClosedPiper J5C · Seaside, ORJul 14, 1967 · N68295 · DestroyedNTSB SEA68F0089Accident · No injuriesClosedCessna 185 · Billings, MTJul 14, 1967 · N4001Y · Substantial damageNTSB LAX68F0071Accident · Serious injuriesClosedPiper PA-24 · Flagstaff, AZJul 14, 1967 · N7367P · DestroyedNTSB CHI68F0257Accident · No injuriesClosedCessna 175 · Byron Center, MIJul 14, 1967 · N9429B · Substantial damageNTSB CHI68F0422Accident · Minor injuriesClosedStinson HW-75 · Havana, ILJul 13, 1967 · N22517 · Substantial damageNTSB ANC68D0015Accident · No injuriesClosedPiper PA-22 · Galena, AKJul 13, 1967 · N5110Z · Substantial damageNTSB NYC68F0136Accident · Serious injuriesClosedAeronca 11BC · Rochester, NYJul 12, 1967 · N3960E · DestroyedNTSB DEN68D0042Accident · No injuriesClosedPiper PA-22 · Ramah, NMJul 12, 1967 · N3272B · Substantial damageNTSB CHI68F0189Accident · No injuriesClosedCessna 150G · Traverse Cty, MIJul 11, 1967 · N8464J · Substantial damageNTSB CHI68D0306Accident · No injuriesClosedBeech A23 · Eau Claire, WIJul 11, 1967 · N3569R · Substantial damageNTSB SEA68A0006Accident · No injuriesClosedBell 47G-3 · Fairfield, MTJul 10, 1967 · N8414E · Substantial damageNTSB FTW68D0090Accident · No injuriesClosedCessna 150E · Glen Rose, TXJul 10, 1967 · N2556J · Substantial damageNTSB OAK68A0004Accident · 1 fatalClosedCessna 336 · Bishop, CAJul 9, 1967 · N3866U · DestroyedNTSB NYC68D0065Accident · No injuriesClosedSchleicher K7 · Middlesex, NYJul 9, 1967 · N4771G · Substantial damageNTSB MKC68F0110Accident · No injuriesClosedCessna 150 · St.Cloud, MNJul 9, 1967 · N4462U · Substantial damageNTSB DEN68F0055Accident · No injuriesClosedPiper PA-18 · Broomfield, COJul 9, 1967 · N8685D · DestroyedNTSB CHI68A0005Accident · 1 fatalClosedPiper PA-24 · Tomah, WIJul 9, 1967 · N7650P · DestroyedNTSB OAK68F0074Accident · Serious injuriesClosedCessna 140 · San Carlos, CAJul 8, 1967 · N81043 · Substantial damageNTSB DEN68D0039Accident · No injuriesClosedSchweizer SGS126 · Espanola, NMJul 8, 1967 · N8676R · Substantial damageNTSB SEA68O0002Accident · Minor injuriesClosedPiper PA-22 · Big Creek Bc, CanadaJul 7, 1967 · N3017Z · DestroyedNTSB NYC68A0003Accident · 2 fatalClosedStinson 108-3 · Woodstock, CTJul 6, 1967 · N595C · DestroyedNTSB FTW68D0069Accident · No injuriesClosedPiper J3C-65 · Canyon, TXJul 6, 1967 · N70653 · Substantial damageNTSB DIA68F0035Accident · Minor injuriesClosedCessna 172G · Virginia Beach, VAJul 6, 1967 · N5759R · Substantial damageNTSB DEN68A0002Accident · No injuriesClosedBell 47G3B1 · Winston, NMJul 6, 1967 · N1320X · Substantial damageNTSB DIA68F0027Accident · No injuriesClosedCessna 172G · Clinton, MDJul 5, 1967 · N3876L · Substantial damageNTSB CHI68F0241Accident · No injuriesClosedLake LA-4 · Union, MIJul 5, 1967 · N2001L · Substantial damageNTSB SEA68A0003Accident · 2 fatalClosedAeronca 7DC · Inchelium, WAJul 4, 1967 · N83685 · Substantial damageNTSB NYC68F0266Accident · Serious injuriesClosedPiper PA-22 · Block Island, RIJul 4, 1967 · N2768P · DestroyedNTSB MIA68D0045Accident · No injuriesClosedBoeing A75N1 · Tallassee, ALJul 4, 1967 · N5521N · Substantial damageNTSB DEN68D0018Accident · No injuriesClosedPiper PA-22 · Hanksville, UTJul 4, 1967 · N3326B · Substantial damageNTSB CHI68F0174Accident · 1 fatalClosedPiper PA-22 · Chicago, ILJul 4, 1967 · N3054Z · DestroyedNTSB CHI68F0029Accident · Minor injuriesClosedStinson 108-2 · Washington Is, WIJul 4, 1967 · N8102K · Substantial damageNTSB CHI68F0111Accident · Serious injuriesClosedZmick Black · Bad Axe, MIJul 3, 1967 · N4679T · DestroyedNTSB FTW68D0082Accident · Minor injuriesClosedCessna 172 · Huttig, ARJul 2, 1967 · N3624S · Substantial damageNTSB ANC68F0031Accident · No injuriesClosedCessna 180 · Iliamna, AKJul 2, 1967 · N7651A · Substantial damageNTSB UNK67X0133Accident · 1 fatalClosedCessna 172 · Holland Ldg, CanadaJul 1, 1967 · N8706U · Substantial damageNTSB MIA68F0086Accident · Minor injuriesClosedGlobe GC-1B · Birmingham, ALJul 1, 1967 · N3815K · Substantial damageNTSB MIA68D0054Accident · Serious injuriesClosedPiper J-3 · Dothan, ALJul 1, 1967 · N42999 · DestroyedNTSB LAX68D0077Accident · No injuriesClosedBoeing A75N1 · Buttonwillow, CAJul 1, 1967 · N56439 · DestroyedNTSB SEA68D0061Accident · No injuriesClosedPiper PA-18 · Tampico, MTJun 30, 1967 · N2883Z · Substantial damageNTSB OAK68D0072Accident · No injuriesClosedTaylorcraft BC12-D · Fremont, CAJun 30, 1967 · N43275 · Substantial damageNTSB MKC68F0036Accident · No injuriesClosedCessna 150F · St Clair, MOJun 30, 1967 · N6777F · Substantial damage