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 CHI69F0186Accident · Serious injuriesClosedPiper PA-22 · Dekalb, ILAug 17, 1968 · N9334D · Substantial damageNTSB CHI69A0019Accident · 1 fatalClosedPiper PA-32 · Brunswick, OHAug 15, 1968 · N3393W · DestroyedNTSB ANC69D0037Accident · No injuriesClosedCessna 180B · Iliamna, AKAug 15, 1968 · N9157T · Substantial damageNTSB SEA69D0067Accident · Minor injuriesClosedCessna 170 · Stanley, IDAug 13, 1968 · N68586 · Substantial damageNTSB CHI69D0208Accident · No injuriesClosedPiper PA-28 · Washington Is, WIAug 13, 1968 · N9809W · Substantial damageNTSB CHI69D0170Accident · No injuriesClosedCessna 170A · Decker, INAug 13, 1968 · N1214D · Substantial damageNTSB SEA69D0123Accident · Minor injuriesClosedPiper PA-18 · Melville, MTAug 12, 1968 · N9443D · DestroyedNTSB CHI69D0148Accident · No injuriesClosedCessna 150 · Coloma, MIAug 12, 1968 · N8987S · Substantial damageNTSB OAK69F0107Accident · Serious injuriesClosedPiper PA-28 · Tahoe Valley, CAAug 11, 1968 · N9731J · DestroyedNTSB MKC69D0228Accident · No injuriesClosedAeronca 11BC · Manfred, NDAug 11, 1968 · N4029E · Substantial damageNTSB MKC69D0116Accident · No injuriesClosedCessna A188 · Scott City, KSAug 11, 1968 · N8081V · Substantial damageNTSB CHI69F0150Accident · No injuriesClosedErco 415-CD · Rio, WIAug 11, 1968 · N3920H · DestroyedNTSB NYC69A0018Accident · Minor injuriesClosedBeech D18S · Jamaica, NYAug 10, 1968 · N7110N · DestroyedNTSB DEN69F0083Accident · Serious injuriesClosedCessna 177 · Albuquerque, NMAug 10, 1968 · N2240Y · Substantial damageNTSB FTW69F0136Accident · Serious injuriesClosedCessna 150 · Sulphur Srngs, TXAug 9, 1968 · N4155U · Substantial damageNTSB CHI69F0164Accident · Minor injuriesClosedPiper PA-28 · Washington, ILAug 9, 1968 · N7118R · Substantial damageNTSB NYC69F0118Accident · Serious injuriesClosedPiper PA-32 · Morrisville, PAAug 8, 1968 · N3709W · DestroyedNTSB MKC69D0115Accident · No injuriesClosedBoeing A75 · New Richland, MNAug 8, 1968 · N54027 · Substantial damageNTSB OAK69F0083Accident · No injuriesClosedKaman K-600 · Orovada, NVAug 7, 1968 · N11119 · Substantial damageNTSB MKC69A0009Accident · 2 fatalClosedCessna 170 · Howard Lake, MNAug 7, 1968 · N3901V · DestroyedNTSB CHI69I0015Incident · No injuriesClosedConvair 880 · Chicago, ILAug 7, 1968 · N828TW · Minor damageNTSB CHI69A0014Accident · Serious injuriesClosedCessna 411 · Fort Wayne, INAug 7, 1968 · N4928T · Substantial damageNTSB FTW69F0152Accident · No injuriesClosedPiper PA-25 · Parkin, ARAug 6, 1968 · N6421Z · Substantial damageNTSB MIA69F0208Accident · Minor injuriesClosedCessna 150 · Jonesboro, GAAug 5, 1968 · N8234S · DestroyedNTSB CHI69F0153Accident · Minor injuriesClosedCessna 140 · Conklin, MIAug 5, 1968 · N76925 · Substantial damageNTSB NYC69D0116Accident · No injuriesClosedCessna 172 · Cicero, NYAug 4, 1968 · N3659S · Substantial damageNTSB MKC69F0087Accident · No injuriesClosedCessna 188 · Elgin, NEAug 4, 1968 · N9803V · Substantial damageNTSB MKC69D0103Accident · No injuriesClosedCessna 172 · Isle, MNAug 4, 1968 · N8522B · Substantial damageNTSB LAX69F0108Accident · Minor injuriesClosedRyan ST3KR · Capistrano, CAAug 4, 1968 · N56711 · DestroyedNTSB CHI69A0013Accident · 5 fatalClosedCessna 172E · S.Bass Islnd, OHAug 4, 1968 · N3793S · DestroyedNTSB ANC69A0006Accident · No injuriesClosedCessna 185 · Kodiak, AKAug 4, 1968 · N2227T · Substantial damageNTSB OAK69D0094Accident · No injuriesClosedBoeing E75 · Traver, CAAug 3, 1968 · N68805 · DestroyedNTSB NYC69F0103Accident · Minor injuriesClosedPiper PA-24 · Thomasville, PAAug 3, 1968 · N7373P · Substantial damageNTSB MKC69D0108Accident · No injuriesClosedPiper PA-18A · Concordia, KSAug 3, 1968 · N4891A · DestroyedNTSB MIA69F0053Accident · No injuriesClosedPiper PA-25 · Harpersville, ALAug 3, 1968 · N6465Z · Substantial damageNTSB MIA69A0008Accident · No injuriesClosedDouglas DC-3C · Walls, MSAug 3, 1968 · N52935 · Substantial damageNTSB LAX69F0080Accident · Serious injuriesClosedCessna 170 · Winslow, AZAug 3, 1968 · N9013A · DestroyedNTSB FTW69F0127Accident · Serious injuriesClosedCessna A188 · Texline, TXAug 3, 1968 · N8148V · Substantial damageNTSB OAK69F0090Accident · Serious injuriesClosedBeech K35 · Ukiah, CAAug 1, 1968 · N9515R · DestroyedNTSB NYC69A0015Accident · No injuriesClosedCessna 337B · New Bedford, MAAug 1, 1968 · N2397S · DestroyedNTSB MIA69F0146Accident · Serious injuriesClosedPiper PA-18A · Fayetteville, TNAug 1, 1968 · N2411P · DestroyedNTSB CHI69D0138Accident · No injuriesClosedCessna 150 · Ossian, INAug 1, 1968 · N6635T · Substantial damageNTSB FTW69F0120Accident · No injuriesClosedCessna 170A · Piedmont, OKJul 31, 1968 · N9572A · Substantial damageNTSB NYC69F0070Accident · Minor injuriesClosedBell 47G-4 · Newport, PAJul 30, 1968 · N1165W · Substantial damageNTSB MKC69D0089Accident · No injuriesClosedGrumman G-164 · New Sharon, IAJul 29, 1968 · N549Y · Substantial damageNTSB CHI69F0231Accident · Minor injuriesClosedDyke Delta JD-2 · Rockford, ILJul 29, 1968 · N555A · Substantial damageNTSB CHI69F0154Accident · Serious injuriesClosedBeech A23 · Monroe, MIJul 29, 1968 · N7969L · DestroyedNTSB DEN69F0069Accident · Serious injuriesClosedCessna 150 · Lbuquerque, NMJul 28, 1968 · N7931F · DestroyedNTSB DEN69D0072Accident · No injuriesClosedPiper PA-23 · Jackson, WYJul 28, 1968 · N690B · Substantial damageNTSB CHI69A0011Accident · No injuriesClosedMystere Falcon · Cleveland, OHJul 28, 1968 · N367EJ · Destroyed