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 ANC72FAG41Accident · No injuriesClosedStinson 108-3 · Bethel, AKAug 12, 1971 · N6031M · Substantial damageNTSB MKC72FCG11Accident · Minor injuriesClosedHiller UH-12A · Springfield, MOAug 11, 1971 · N4287C · Substantial damageNTSB MIA72FLG07Accident · No injuriesClosedPiper PA-28 · Greenville, SCAug 11, 1971 · N8820N · Substantial damageNTSB CHI72FEV09Accident · No injuriesClosedPiper PA-25 · Perryville, MOAug 11, 1971 · N6221Z · Substantial damageNTSB DEN72FTC03Accident · Serious injuriesClosedPiper J3C-65 · Midwest, WYAug 9, 1971 · N70128 · Substantial damageNTSB DEN72FQA10Accident · No injuriesClosedPiper PA-28 · Albuquerque, NMAug 9, 1971 · N5374L · Substantial damageNTSB ANC72DAG16Accident · No injuriesClosedPiper PA-18 · Glenallen, AKAug 9, 1971 · N4473Z · Substantial damageNTSB NYC72FNE04Accident · No injuriesClosedCessna 140 · Meriden, CTAug 8, 1971 · N2913N · Substantial damageNTSB NYC72AN022Accident · 1 fatalClosedCessna 170B · Wellsboro, PAAug 8, 1971 · N8155A · Substantial damageNTSB MKC72FER14Accident · 1 fatalClosedPiper PA-18 · Rosemont, MNAug 8, 1971 · N4187Z · DestroyedNTSB FTW72FRD04Accident · No injuriesClosedStinson 108-1 · De Quincy, LAAug 8, 1971 · N9098K · Substantial damageNTSB DEN72FQA09Accident · No injuriesClosedHelio Acft H-395 · Albuquerque, NMAug 8, 1971 · N4150D · Substantial damageNTSB CHI72FEG04Accident · Minor injuriesClosedCessna 172 · Cincinnati, OHAug 8, 1971 · N4317L · Substantial damageNTSB NYC72FGM09Accident · Serious injuriesClosedErco 415C · Irwin, PAAug 7, 1971 · N99162 · Substantial damageNTSB NYC72DFJ09Accident · No injuriesClosedCessna 150 · Geneseo, NYAug 7, 1971 · N5769E · Substantial damageNTSB MIA72FKG19Accident · Serious injuriesClosedGlobe GC-1B · Fairburn, GAAug 7, 1971 · N90338 · Substantial damageNTSB LAX72FUJ25Accident · Minor injuriesClosedCessna 402A · Scottsdale, AZAug 7, 1971 · N4598Q · Substantial damageNTSB DEN72DTE09Accident · No injuriesClosedPiper PA-25 · Center, COAug 7, 1971 · N7496Z · Substantial damageNTSB NYC72FFA06Accident · No injuriesClosedCessna 150G · Gloversville, NYAug 5, 1971 · N4766X · Substantial damageNTSB MKC72AK009Accident · 1 fatalClosedCessna 172 · Bridgewater, IAAug 5, 1971 · N6389E · DestroyedNTSB LAX72DUJ22Accident · No injuriesClosedPiper PA-25 · Parker, AZAug 5, 1971 · N6570Z · Substantial damageNTSB CHI72FEP06Accident · Minor injuriesClosedDavis V-3 · Wauwatosa, WIAug 5, 1971 · NC630 · Substantial damageNTSB CHI72AC012Accident · No injuriesClosedBeech 99 · Ashland, WIAug 5, 1971 · N7099N · Substantial damageNTSB MKC72FERI2Accident · Serious injuriesClosedAeronca 15AC · Warroad, MNAug 4, 1971 · N1443H · DestroyedNTSB MKC72DCD19Accident · No injuriesClosedPiper PA-25 · Olin, IAAug 4, 1971 · N7036Z · Substantial damageNTSB DEN72FTE07Accident · No injuriesClosedPiper PA-24 · Denver, COAug 4, 1971 · N4960 · Substantial damageNTSB MIA72DLA04Accident · No injuriesClosedAero Comdr A-9 · Altha, FLAug 3, 1971 · N8400V · DestroyedNTSB FTW72DQG12Accident · No injuriesClosedCessna 320B · Lubbock, TXAug 2, 1971 · N99004 · Substantial damageNTSB SEA72FYE04Accident · No injuriesClosedJeanies Teenie · Kent, WAAug 1, 1971 · N2457 · Substantial damageNTSB CHI72DEP10Accident · Minor injuriesClosedPiper J-3 · East Troy, WIAug 1, 1971 · N3676K · Substantial damageNTSB CHI72FER01Accident · Minor injuriesClosedPiper PA-28 · Trego, WIJul 31, 1971 · N5657U · Substantial damageNTSB NYC72AN016Accident · 1 fatalClosedGrumman G-21A · Greenville, MEJul 30, 1971 · N101LH · DestroyedNTSB DCA72AZ002Accident · Serious injuriesClosedBoeing 747 · San Frncsco, CAJul 30, 1971 · N747PA · Substantial damageNTSB NYC72AN014Accident · 1 fatalClosedPiper PA-18 · Essex, NYJul 29, 1971 · N16689 · DestroyedNTSB DEN72FQA08Accident · No injuriesClosedMiniplane Faler · Gallup, NMJul 29, 1971 · N2117 · Substantial damageNTSB MIA72RM007Accident · 1 fatalClosedPiper PA-30 · T.Cay, Burkina FasoJul 28, 1971 · N7009Y · DestroyedNTSB FTW72FQG07Accident · 1 fatalClosedPiper PA-18 · Texline, TXJul 28, 1971 · N8033P · DestroyedNTSB SEA72FYC11Accident · No injuriesClosedCessna 150 · Albany, ORJul 27, 1971 · N3625J · Substantial damageNTSB NYC72FNC05Accident · No injuriesClosedCessna 170B · Brookline, NHJul 27, 1971 · N4586C · Substantial damageNTSB NYC72FFJ07Accident · No injuriesClosedPiper PA-20 · Palmyra, NYJul 27, 1971 · N6984K · Substantial damageNTSB MKC72FER08Accident · Minor injuriesClosedPiper PA-28 · New Ulm, MNJul 27, 1971 · N7435R · Substantial damageNTSB SEA72AS004Accident · No injuriesClosedDouglas DC-3C · Redmond, ORJul 26, 1971 · N51041 · Substantial damageNTSB NYC72AN011Accident · 2 fatalClosedPiper PA-30 · Madison, CTJul 26, 1971 · N643TT · DestroyedNTSB OAK72FXQ03Accident · Minor injuriesClosedCessna 177 · Quincy, CAJul 25, 1971 · N2814X · Substantial damageNTSB NYC72FFA04Accident · Minor injuriesClosedStinson 108-2 · Malone, NYJul 25, 1971 · N9453K · DestroyedNTSB IAD72FID02Accident · Minor injuriesClosedCessna 182 · Headwaters, VAJul 25, 1971 · N1855X · DestroyedNTSB ANC72DAG12Accident · No injuriesClosedStinson 108-2 · Kodiak, AKJul 25, 1971 · N9574K · DestroyedNTSB NYC72FHJ02Accident · No injuriesClosedNavion A · Bohemia, NYJul 24, 1971 · N4502K · Substantial damageNTSB DEN72FQA06Accident · No injuriesClosedPiper PA-32 · Albuquerque, NMJul 24, 1971 · N3507W · Substantial damageNTSB MKC72FCJ08Accident · No injuriesClosedBeech J35 · Ogallala, NEJul 23, 1971 · N8345D · Substantial damage