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 ANC68F0088Accident · No injuriesClosedPiper PA-18 · Gulkana, AKSep 21, 1967 · N2855T · Substantial damageNTSB SEA68D0144Accident · Minor injuriesClosedTaylorcraft BC12-D · Harrison, MTSep 20, 1967 · N95731 · Substantial damageNTSB CHI68F0278Accident · No injuriesClosedBeech 35-33 · Grove City, OHSep 20, 1967 · N607V · Substantial damageNTSB LAX68D0147Accident · No injuriesClosedPiper PA-15 · Agua Dulce, CASep 19, 1967 · N4193H · Substantial damageNTSB SEA68F0173Incident · Minor injuriesClosedBeech A23 · Woodburn, ORSep 18, 1967 · CF-VVH · DestroyedNTSB NYC68A0047Accident · Serious injuriesClosedMooney M20C · Jamaica, NYSep 17, 1967 · N6251U · DestroyedNTSB DEN68F0098Accident · No injuriesClosedCessna 150 · Lamar, COSep 17, 1967 · N6623F · Substantial damageNTSB OAK68A0028Accident · Minor injuriesClosedBell 47G3B1 · S.Lak Tahoe, CASep 16, 1967 · N7829S · Substantial damageNTSB MKC68D0297Accident · No injuriesClosedGlobe GC-1B · Harrisonville, MOSep 16, 1967 · N3843K · Substantial damageNTSB NYC68F0190Accident · No injuriesClosedHelio Acft H-295 · Topsfield, MASep 15, 1967 · N269E · Substantial damageNTSB MIA68D0194Accident · No injuriesClosedBeech 35 · Kissimee, FLSep 15, 1967 · N3295V · Substantial damageNTSB MIA68A0029Accident · No injuriesClosedBell 47H-1 · Miami, FLSep 15, 1967 · N2475B · Substantial damageNTSB ANC68A0018Accident · No injuriesClosedHiller UH-12E · Chicken, AKSep 15, 1967 · N5361V · Substantial damageNTSB DEN68D0115Accident · No injuriesClosedPiper PA-24 · Grady, NMSep 14, 1967 · N8029P · Substantial damageNTSB OAK68A0027Accident · Serious injuriesClosedGrumman TBM-3E · Hollister, CASep 13, 1967 · N9318Z · DestroyedNTSB MIA68O0003Accident · No injuriesClosedCurtiss-Wrt C-46F · Kingston, JamaicaSep 13, 1967 · N1309V · DestroyedNTSB OAK68D0139Accident · No injuriesClosedTaylorcraft L-2 · Castroville, CASep 10, 1967 · N66713 · Substantial damageNTSB CHI68F0336Accident · Serious injuriesClosedPiper PA-22 · Greenfield, INSep 10, 1967 · N3740P · 2 aircraft · Substantial damageNTSB FTW68F0221Accident · No injuriesClosedLuscombe 8F · Lafayette, LASep 9, 1967 · N9916C · Substantial damageNTSB FTW68D0220Accident · No injuriesClosedPiper PA-22 · Marshall, OKSep 9, 1967 · N6946D · Substantial damageNTSB DEN68F0084Accident · Minor injuriesClosedGlobe GC-1B · Littleton, COSep 8, 1967 · N782E · Substantial damageNTSB DEN68A0018Accident · 3 fatalClosedBeech C-45H · Farmington, NMSep 8, 1967 · N9507Z · DestroyedNTSB OAK68A0026Accident · 2 fatalClosedPiper PA-22 · Crescent City, CASep 7, 1967 · N3621A · DestroyedNTSB DIA68F0077Accident · Serious injuriesClosedPiper PA-28 · Federalsburg, MDSep 7, 1967 · N6515W · Substantial damageNTSB CHI68F0264Accident · Serious injuriesClosedStinson 108-3 · Lore City, OHSep 7, 1967 · N6421M · Substantial damageNTSB CHI68A0025Accident · 2 fatalClosedErco 415C · Hillsdale, ILSep 7, 1967 · N99034 · Substantial damageNTSB SEA68F0131Accident · Serious injuriesClosedChampion 7KCAB · North Bend, ORSep 6, 1967 · N2728G · Substantial damageNTSB NYC68F0132Accident · No injuriesClosedBeech 95-C55 · Lehighton, PASep 6, 1967 · N2710T · Substantial damageNTSB SEA68D0153Accident · Minor injuriesClosedStinson 108-2 · Bellingham, WASep 4, 1967 · CF-JGU · Substantial damageNTSB DEN68D0093Accident · Serious injuriesClosedStinson 108-3 · Gallup, NMSep 4, 1967 · N6237M · Substantial damageNTSB OAK68F0206Accident · No injuriesClosedBoeing A75N1 · Sacramento, CASep 3, 1967 · N1713B · Substantial damageNTSB OAK68A0024Accident · 2 fatalClosedCessna 120 · Tracy, CASep 3, 1967 · N1800N · DestroyedNTSB SEA68A0022Accident · Serious injuriesClosedEnstrom F-28 · Santiam Jct, ORSep 2, 1967 · N192E · DestroyedNTSB NYC68A0040Accident · 2 fatalClosedLuscombe 8A · Morgan, PASep 2, 1967 · N77955 · DestroyedNTSB MKC68F0207Accident · No injuriesClosedTaylorcraft BC12-D · Crane Lake, MNSep 2, 1967 · N5290M · Substantial damageNTSB MIA68D0240Accident · No injuriesClosedCessna 172 · Pachuca, MexicoSep 2, 1967 · N8119B · Substantial damageNTSB CHI68A0023Accident · 2 fatalClosedCessna 170B · Ludington, MISep 2, 1967 · N3563C · DestroyedNTSB ANC68F0097Accident · Serious injuriesClosedPiper PA-18 · Chitina, AKSep 1, 1967 · N2285Z · DestroyedNTSB NYC68F0124Accident · No injuriesClosedPiper PA-20S · Bucksport, MEAug 30, 1967 · N1835A · Substantial damageNTSB FTW68F0257Accident · No injuriesClosedGrumman G-164 · Reserve, LAAug 30, 1967 · N463RF · Substantial damageNTSB DIA68F0079Accident · No injuriesClosedStinson HW-75 · Clinton, NCAug 30, 1967 · N21138 · Substantial damageNTSB SEA68A0020Accident · Serious injuriesClosedBoeing B-17 · Kalispell, MTAug 29, 1967 · N7228C · DestroyedNTSB NYC68A0034Accident · No injuriesClosedSikorsky S-51 · Wndsor Locks, CTAug 29, 1967 · N13301 · Substantial damageNTSB ANC68D0049Accident · Minor injuriesClosedPiper PA-12S · Ketchikan, AKAug 29, 1967 · N3993M · DestroyedNTSB DIA68F0060Accident · Minor injuriesClosedPiper J-3 · Greensboro, NCAug 28, 1967 · N41127 · Substantial damageNTSB OAK68F0126Accident · Serious injuriesClosedBoeing A75 · Selma, CAAug 27, 1967 · N54896 · DestroyedNTSB MIA68F0163Accident · No injuriesClosedPiper J3 · Bushnell, FLAug 27, 1967 · N35737 · Substantial damageNTSB FTW68F0213Accident · No injuriesClosedPiper PA-22 · Quinton, OKAug 27, 1967 · N1330C · Substantial damageNTSB FTW68D0192Accident · Minor injuriesClosedPiper PA-22 · Amarillo, TXAug 27, 1967 · N2501A · Substantial damageNTSB DIA68A0009Accident · 1 fatalClosedAeronca 11CC · Enoch, WVAug 27, 1967 · N2094A · Destroyed