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 SEA67D0090Accident · No injuriesClosedPiper PA-18 · Hamer, IDJul 23, 1966 · N7083D · Substantial damageNTSB NYC67F0126Accident · No injuriesClosedBoeing D75 · Colts Neck, NJJul 23, 1966 · N59269 · Substantial damageNTSB MKC67F0110Accident · No injuriesClosedSnow S2A · Elwood, NEJul 23, 1966 · N3699F · Substantial damageNTSB DEN67F0068Accident · Serious injuriesClosedAeronca 65-TC · Greybull, WYJul 23, 1966 · N27392 · Substantial damageNTSB DEN67D0047Accident · No injuriesClosedCessna 172 · Quemado, NMJul 23, 1966 · N8182B · DestroyedNTSB MKC67F0136Accident · No injuriesClosedHelio H-250 · Hot Springs, SDJul 22, 1966 · N6318V · Substantial damageNTSB LAX67F0065Accident · Minor injuriesClosedPiper PA-32 · Walteria Sta., CAJul 22, 1966 · N3403W · Substantial damageNTSB FTW67D0106Accident · No injuriesClosedGrumman G-164 · Port Lavaca, TXJul 22, 1966 · N485Y · Substantial damageNTSB LAX67F0053Accident · Serious injuriesClosedPiper PA-22 · Tucson, AZJul 21, 1966 · N3317B · DestroyedNTSB FTW67F0229Accident · No injuriesClosedCessna 172 · Grand Prairie, TXJul 21, 1966 · N8261U · Substantial damageNTSB ANC67A0012Accident · No injuriesClosedBell 47G-2 · Copper Cntr, AKJul 21, 1966 · N6141 · Substantial damageNTSB LAX67A0009Accident · Minor injuriesClosedHughes 269A · Culver City, CAJul 19, 1966 · N8851F · Substantial damageNTSB LAX67A0008Accident · 1 fatalClosedCessna 170A · Santa Maria, CAJul 19, 1966 · N1488D · DestroyedNTSB CHI67F0302Accident · 1 fatalClosedPiper PA-18A · Sandwich, ILJul 19, 1966 · N7192D · DestroyedNTSB NYC67F0119Accident · No injuriesClosedPiper PA-25 · Worthington, MAJul 18, 1966 · N6174Z · Substantial damageNTSB ANC67D0031Accident · No injuriesClosedPiper PA-18A · Anchorage, AKJul 18, 1966 · N7167D · Substantial damageNTSB NYC67F0087Accident · No injuriesClosedCessna 205 · Kingfield, MEJul 17, 1966 · N1820Z · Substantial damageNTSB FTW67F0095Accident · Minor injuriesClosedPiper J-3 · Tomball, TXJul 16, 1966 · N75088 · Substantial damageNTSB CHI67D0212Accident · No injuriesClosedStinson 10A · Rockton, ILJul 16, 1966 · N32218 · Substantial damageNTSB ANC67A0007Accident · Minor injuriesClosedCessna 180 · Kodiak, AKJul 16, 1966 · N9737B · DestroyedNTSB UNK66X0157Accident · No injuriesClosedCessna 337 · Somalild, DjiboutiJul 15, 1966 · N2148X · Substantial damageNTSB NYC67A0013Accident · 2 fatalClosedPiper J3C-65 · Clarion, PAJul 15, 1966 · N32916 · DestroyedNTSB MKC67F0611Accident · Serious injuriesClosedPiper PA-18 · Stanwood, IAJul 15, 1966 · N5697Y · DestroyedNTSB NYC67D0167Accident · No injuriesClosedCessna 150 · New City, NYJul 13, 1966 · N8128F · Substantial damageNTSB MIA66A0006Accident · No injuriesClosedHughes 269B · Arecibo, PRJul 13, 1966 · N9395F · Substantial damageNTSB DEN67D0113Accident · Serious injuriesClosedBeech 35 · Columbus, NMJul 13, 1966 · N3253 · DestroyedNTSB NYC67F0058Accident · No injuriesClosedPiper PA-22 · Prospectville, PAJul 12, 1966 · N4736Z · Substantial damageNTSB OAK67A0005Accident · 1 fatalClosedPiper PA-23 · San Jose, CAJul 11, 1966 · N5550V · DestroyedNTSB NYC67D0080Accident · No injuriesClosedPiper J-3 · Erma, NJJul 11, 1966 · N46286 · Substantial damageNTSB FTW67F0092Accident · No injuriesClosedBoeing A75 · Swan Lake, ARJul 11, 1966 · N63711 · Substantial damageNTSB CHI67D0105Accident · Minor injuriesClosedPiper PA-22 · Decatur, INJul 11, 1966 · N8942D · Substantial damageNTSB ANC67D0009Accident · No injuriesClosedCessna 180 · Manley Sprgs, AKJul 11, 1966 · N7740A · DestroyedNTSB SEA67D0036Accident · No injuriesClosedCessna 182 · Riggins, IDJul 10, 1966 · N3401S · Substantial damageNTSB NYC67F0088Accident · Minor injuriesClosedPiper PA-24 · Chester, MDJul 10, 1966 · N8361P · Substantial damageNTSB NYC67F0032Accident · No injuriesClosedPiper J365 · Ft Kent, MEJul 10, 1966 · N30779 · Substantial damageNTSB MKC67F0190Accident · No injuriesClosedVollmer VJ-22 · Waubun, MNJul 10, 1966 · N4634T · Substantial damageNTSB MKC67D0113Accident · No injuriesClosedBeech A23 · Central City, IAJul 10, 1966 · N8793M · Substantial damageNTSB CHI67F0076Accident · No injuriesClosedPiper J-3 · Dagget, MIJul 10, 1966 · N6240H · Substantial damageNTSB CHI67D0097Accident · Minor injuriesClosedWaco UPF7 · Richmond, INJul 10, 1966 · N32026 · Substantial damageNTSB MIA67A0005Accident · 2 fatalClosedRepublic RC-3 · Jonesboro, GAJul 9, 1966 · N6697K · Substantial damageNTSB CHI67A0005Accident · No injuriesClosedBell 47G-2A · Slemp, KYJul 9, 1966 · N73219 · Substantial damageNTSB MIA67A0004Accident · No injuriesClosedHughes 269B · Cope, SCJul 7, 1966 · N9410F · Substantial damageNTSB DEN67A0002Accident · Minor injuriesClosedHiller UH12E · Greybull, WYJul 7, 1966 · N9754C · Substantial damageNTSB OAK67D0032Accident · No injuriesClosedCessna 150 · Kauakakai, HIJul 6, 1966 · N2061Z · Substantial damageNTSB LAX67A0002Accident · 4 fatalClosedMooney M20C · Lake Havasu, AZJul 6, 1966 · N5907Q · DestroyedNTSB ANC67D0006Accident · No injuriesClosedLuscombe 8E · Beaver, AKJul 6, 1966 · N2252B · Substantial damageNTSB OAK67D0043Accident · Serious injuriesClosedCessna 170B · Contact, NVJul 5, 1966 · N8201A · Substantial damageNTSB FTW67F0268Accident · Minor injuriesClosedPiper PA-24 · Keene, TXJul 5, 1966 · N5081P · Substantial damageNTSB CHI67F0141Accident · Serious injuriesClosedBeech A23 · Baraboo, WIJul 5, 1966 · N1449L · Substantial damageNTSB SEA67F0084Accident · No injuriesClosedCessna 150 · Cottage Grove, ORJul 4, 1966 · N6604T · Substantial damage