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 IAD70D0109Accident · No injuriesClosedMooney M20F · Hendersonville, NCNov 27, 1969 · N9396M · Substantial damageNTSB FTW70F0307Accident · Minor injuriesClosedMooney M20C · Edmond, OKNov 27, 1969 · N3988N · Substantial damageNTSB LAX70F0210Accident · Serious injuriesClosedBeech F35 · Tucson, AZNov 26, 1969 · N3230C · Substantial damageNTSB IAD70D0128Accident · No injuriesClosedBeech B35 · Norfolk, VANov 26, 1969 · N5168C · Substantial damageNTSB OAK70F0171Accident · Minor injuriesClosedAeronca 15AC · Reno, NVNov 25, 1969 · N1149H · Substantial damageNTSB NYC70F0215Accident · Serious injuriesClosedCessna 140 · Hammondsport, NYNov 23, 1969 · N1639V · DestroyedNTSB NYC70D0220Accident · Minor injuriesClosedCessna 172 · Wilmington, VTNov 23, 1969 · N84007 · DestroyedNTSB NYC70F0231Accident · No injuriesClosedBeech 23 · Mattituck, NYNov 22, 1969 · N2361Q · Substantial damageNTSB IAD70D0122Accident · No injuriesClosedLuscombe 8A · Norfolk, VANov 22, 1969 · N2114K · Substantial damageNTSB LAX70A0031Accident · No injuriesClosedBell 47J · Goleta, CANov 21, 1969 · N73921 · Substantial damageNTSB MIA70F0240Accident · No injuriesClosedBoeing A75N1 · Immokalee, FLNov 17, 1969 · N52126 · Substantial damageNTSB DEN70F0160Accident · Minor injuriesClosedFairchild 24W · Colo.Spgs, CONov 16, 1969 · N28686 · Substantial damageNTSB DEN70F0141Accident · No injuriesClosedPiper PA-28 · Alameda, NMNov 16, 1969 · N4604R · Substantial damageNTSB DEN70D0145Accident · No injuriesClosedCessna 120 · Akron, CONov 16, 1969 · N3044N · Substantial damageNTSB DEN70D0133Accident · No injuriesClosedPiper PA-24 · Mayhill, NMNov 16, 1969 · N9180P · Substantial damageNTSB CHI70F0396Accident · Serious injuriesClosedCessna 172F · Strongsville, OHNov 16, 1969 · N5311R · Substantial damageNTSB CHI70F0345Accident · No injuriesClosedPiper PA-22 · Phillipsburg, OHNov 16, 1969 · N3619P · Substantial damageNTSB ANC70A0042Accident · No injuriesClosedCessna 180 · Kwigillingok, AKNov 16, 1969 · N2733X · Substantial damageNTSB MIA70F0239Accident · No injuriesClosedCessna 170A · Marietta, GANov 15, 1969 · N9913A · Substantial damageNTSB IAD70A0033Accident · 1 fatalClosedPiper PA-20 · Woodbridge, VANov 15, 1969 · N7015K · DestroyedNTSB FTW70F0299Accident · No injuriesClosedPiper PA-23 · Plainview, TXNov 15, 1969 · N1065P · Substantial damageNTSB DEN70F0148Accident · Serious injuriesClosedHiller UH-12B · Roswell, NMNov 15, 1969 · N10321 · Substantial damageNTSB CHI70F0380Accident · Serious injuriesClosedCessna 172 · Brookpark, OHNov 14, 1969 · N3688S · DestroyedNTSB CHI70F0339Accident · No injuriesClosedCessna 150 · Wixom, MINov 12, 1969 · N3693J · DestroyedNTSB FTW70F0300Accident · Minor injuriesClosedCessna 185 · Morgan City, LANov 11, 1969 · N32S · Substantial damageNTSB SEA70F0143Accident · Minor injuriesClosedCessna P206 · Independence, ORNov 10, 1969 · N2535X · Substantial damageNTSB MKC70F0266Accident · No injuriesClosedPiper J3C-65 · Midland, SDNov 10, 1969 · N87924 · Substantial damageNTSB DEN70F0131Accident · Minor injuriesClosedCessna 150J · Las Cruces, NMNov 10, 1969 · N51000 · Substantial damageNTSB MKC70F0260Accident · No injuriesClosedPiper PA-18 · Pierre, SDNov 8, 1969 · N3817Z · Substantial damageNTSB FTW70F0290Accident · Serious injuriesClosedPiper J-3 · Muskogee, OKNov 8, 1969 · N6612H · Substantial damageNTSB FTW70F0285Accident · No injuriesClosedPiper PA-28 · Dermott, ARNov 8, 1969 · N6048W · Substantial damageNTSB CHI70A0030Accident · 2 fatalClosedMooney M20E · Barron, WINov 8, 1969 · N1293X · DestroyedNTSB OAK70D0162Accident · Minor injuriesClosedRyan ST-3KR · Sonoma, CANov 2, 1969 · N57081 · DestroyedNTSB CHI70A0023Accident · 1 fatalClosedBeech D18S · Madison, WINov 1, 1969 · N106H · DestroyedNTSB FTW70F0283Accident · Minor injuriesClosedBell 47J · Grand Isle, LAOct 31, 1969 · N5153B · Substantial damageNTSB MKC70D0277Accident · No injuriesClosedPiper J3C-65 · Trading Post, KSOct 29, 1969 · N87942 · Substantial damageNTSB NYC70F0209Accident · Serious injuriesClosedPiper PA-28 · Waterford, CTOct 26, 1969 · N7175W · DestroyedNTSB FTW70F0280Accident · Minor injuriesClosedPiper J3C-65 · Rosenburg, TXOct 26, 1969 · N92462 · Substantial damageNTSB CHI70F0299Accident · Serious injuriesClosedPiper PA-22 · Grand Haven, MIOct 26, 1969 · N6006D · DestroyedNTSB CHI70F0297Accident · No injuriesClosedPiper PA-28 · Rushville, INOct 26, 1969 · N5656U · Substantial damageNTSB ANC70D0072Accident · No injuriesClosedPiper PA-12S · Kodiak, AKOct 25, 1969 · N7716H · Substantial damageNTSB CHI70F0344Accident · Minor injuriesClosedGlobe GC-1B · Wheeling, ILOct 23, 1969 · N78172 · Substantial damageNTSB MKC70F0304Accident · Minor injuriesClosedCessna 150 · Leawood, KSOct 21, 1969 · N4070U · Substantial damageNTSB ANC70F0056Accident · No injuriesClosedPiper PA-32 · Fairbanks, AKOct 21, 1969 · N4160W · Substantial damageNTSB MKC70A0021Accident · 1 fatalClosedBeech B23 · Minneapolis, MNOct 20, 1969 · N741FT · DestroyedNTSB MKC70F0241Accident · Minor injuriesClosedAeronca 65-TAL · Lincoln, NEOct 19, 1969 · N39358 · Substantial damageNTSB FTW70F0269Accident · Serious injuriesClosedGlobe GC-1A · N.Little Rock, AROct 19, 1969 · N80853 · Substantial damageNTSB CHI70F0304Accident · Serious injuriesClosedBeech 95-A55 · Grove City, OHOct 19, 1969 · N36900 · DestroyedNTSB ANC70A0036Accident · No injuriesClosedHughes 269B · Ketchikan, AKOct 19, 1969 · N9441F · Substantial damageNTSB NYC70A0044Accident · 2 fatalClosedCessna 120 · Seneca, PAOct 18, 1969 · N72392 · Destroyed