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 DEN66D0289Accident · No injuriesClosedPiper PA-24 · Tucson, AZApr 21, 1966 · N8242P · Substantial damageNTSB CHI66F0695Accident · No injuriesClosedBeech 35 · Eaton, MIApr 21, 1966 · N2820V · Substantial damageNTSB MKC66F0538Accident · No injuriesClosedPiper PA-23 · Iowa City, IAApr 20, 1966 · N3301P · Substantial damageNTSB DEN66A0077Accident · No injuriesClosedDouglas C47A · Douglas, AZApr 20, 1966 · N86462 · Substantial damageNTSB FTW66F0635Accident · No injuriesClosedBoeing E75N1 · Lake Charles, LAApr 19, 1966 · N75272 · Substantial damageNTSB MIA66F0525Accident · No injuriesClosedPiper PA-28 · Bloomingdale, FLApr 18, 1966 · N6328W · Substantial damageNTSB NYC66F0638Accident · No injuriesClosedHelio H-391 · Orangeburg, NYApr 17, 1966 · N6022 · Substantial damageNTSB MKC66F0563Accident · Minor injuriesClosedCessna 172 · Neodesha, KSApr 17, 1966 · N5638R · Substantial damageNTSB CHI66D0689Accident · No injuriesClosedTaylorcraft BC-12D · Waynesville, OHApr 17, 1966 · N44489 · Substantial damageNTSB ANC66D0103Accident · No injuriesClosedChampion 7HC · Delta Junctn, AKApr 17, 1966 · N8556E · Substantial damageNTSB SEA66F0285Accident · Minor injuriesClosedPiper PA-22 · Maple Valley, WAApr 16, 1966 · N2442P · Substantial damageNTSB OAK67D0016Accident · No injuriesClosedChampion 7GCA · Dixon, CAApr 15, 1966 · N9603S · Substantial damageNTSB FTW66F0613Accident · No injuriesClosedCessna 150; · Pearland, TXApr 14, 1966 · N8635G · Substantial damageNTSB MIA66F0528Accident · Serious injuriesClosedPiper PA-22 · Riverview, FLApr 12, 1966 · N1997P · Substantial damageNTSB DEN66A0074Accident · 1 fatalClosedBell 47G-3B · Grand Canyon, AZApr 12, 1966 · N73909 · DestroyedNTSB FTW66F0619Accident · No injuriesClosedPiper PA-25 · Cleo Springs, OKApr 11, 1966 · N6073Z · Substantial damageNTSB UNK66X0136Accident · 12 fatalClosedBeech C-45H · Nogales, MexicoApr 10, 1966 · N9572Z · DestroyedNTSB SEA66D0244Accident · No injuriesClosedCessna 175 · Challis, IDApr 10, 1966 · N7254M · Substantial damageNTSB OAK66D0275Accident · No injuriesClosedMooney 20C · Mammoth Lks, CAApr 10, 1966 · N79390 · Substantial damageNTSB NYC66D0611Accident · No injuriesClosedPiper PA-22 · Stratford, CTApr 10, 1966 · N6959B · Substantial damageNTSB DEN66F0292Accident · No injuriesClosedBeech 35 · Texico, NMApr 10, 1966 · N440B · Substantial damageNTSB CHI66F0686Accident · No injuriesClosedMonocoupe 110 · Elaware, OHApr 10, 1966 · N372Y · Substantial damageNTSB CHI66F0649Accident · No injuriesClosedPiper PA-28 · Sunbury, OHApr 10, 1966 · N5825W · Substantial damageNTSB CHI66D0721Accident · No injuriesClosedChampion 7ECA · Naperville, ILApr 9, 1966 · N9513S · Substantial damageNTSB MIA66D0521Accident · No injuriesClosedCessna 172 · Bishopville, SCApr 8, 1966 · N2005Y · Substantial damageNTSB OAK66F0267Accident · Serious injuriesClosedRaven S-50 · Livermore, CAApr 7, 1966 · N11997 · Minor damageNTSB SEA66F0234Accident · No injuriesClosedPiper PA-25 · Nezperce, IDApr 6, 1966 · N7686Z · DestroyedNTSB LAX66A0076Accident · 2 fatalClosedCessna 210 · Hemet, CAApr 6, 1966 · N6547X · DestroyedNTSB FTW66F0627Accident · Minor injuriesClosedGlove GC1B · Morgan, TXApr 6, 1966 · N78136 · Substantial damageNTSB DEN66F0277Accident · No injuriesClosedCessna 182A · Salt Lake City, UTApr 5, 1966 · N2120G · Substantial damageNTSB LAX66F0348Accident · Serious injuriesClosedBeech K35 · Bermuda Dunes, CAApr 4, 1966 · N6035E · Substantial damageNTSB FTW66A0087Accident · No injuriesClosedBell 47G-2 · Billings, OKApr 4, 1966 · N939B · Substantial damageNTSB CHI66F0633Accident · No injuriesClosedBeech 35 · Flint, MIApr 3, 1966 · N3804N · Substantial damageNTSB CHI66F0717Accident · Minor injuriesClosedStinson V77 · Naperville, ILApr 2, 1966 · N79129 · Substantial damageNTSB NYC66F0608Accident · Minor injuriesClosedCessna 172 · Roscoe, NYMar 29, 1966 · N8675U · Substantial damageNTSB SEA66F0252Accident · No injuriesClosedBeech B35 · Auburn, WAMar 25, 1966 · N5214C · Substantial damageNTSB OAK66A0040Accident · 1 fatalClosedBeech G35 · Modesto, CAMar 25, 1966 · N113W · DestroyedNTSB FTW67D0212Accident · No injuriesClosedBoeing B75N1 · Manila, ARMar 25, 1966 · N5146N · Substantial damageNTSB CHI66F0650Accident · No injuriesClosedBeech E18S · Quincy, ILMar 25, 1966 · N2427 · Substantial damageNTSB CHI66D0658Accident · No injuriesClosedPiper PA-28 · Shullsburg, WIMar 25, 1966 · N4571R · Substantial damageNTSB SEA66F0238Accident · Minor injuriesClosedBoeing A-75 · Culdesc, IDMar 24, 1966 · N56236 · Substantial damageNTSB SEA66F0223Accident · No injuriesClosedMooney M20C · Scobey, MTMar 24, 1966 · N5996Q · Substantial damageNTSB OAK66F0294Accident · Minor injuriesClosedCessna 210 · Round Mtn., NVMar 23, 1966 · N3717Y · Substantial damageNTSB DEN66D0246Accident · No injuriesClosedLuther Cougar · Montello, NVMar 21, 1966 · N92870 · Substantial damageNTSB MIA66F0489Accident · No injuriesClosedErco 415-C · Punta Gorda, FLMar 20, 1966 · N87292 · Substantial damageNTSB MIA66F0474Accident · No injuriesClosedPiper PA-24 · Cypress Grdns, FLMar 20, 1966 · N6586P · Substantial damageNTSB CHI66F0629Accident · No injuriesClosedMooney M20 · Indianapolis, INMar 20, 1966 · N5279B · Substantial damageNTSB OAK66A0037Accident · 3 fatalClosedPiper PA-23 · El Granada, CAMar 19, 1966 · N2204P · DestroyedNTSB MIA66A0109Accident · No injuriesClosedGrumman G-21A · C.Amalie St.Th., VIMar 19, 1966 · N5548A · Substantial damageNTSB CHI66A0077Accident · 1 fatalClosedAeronca CF · Jonesboro, ILMar 19, 1966 · N22391 · Destroyed