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 LAX70D0056Accident · No injuriesClosedPiper J4E · Yucca Valley, CAJul 26, 1969 · N40792 · Substantial damageNTSB CHI70F0120Accident · No injuriesClosedTaylorcraft BL-65 · Waynesville, OHJul 26, 1969 · N36154 · Substantial damageNTSB CHI70F0075Accident · Minor injuriesClosedChampion 7ECA · W Carrollton, OHJul 26, 1969 · N2506F · Substantial damageNTSB OAK70F0088Accident · Minor injuriesClosedBell 47G3B1 · Fresno, CAJul 25, 1969 · N1130W · Substantial damageNTSB MKC70F0116Accident · No injuriesClosedCessna 150 · Albany, MOJul 25, 1969 · N8955S · Substantial damageNTSB MKC70D0119Accident · No injuriesClosedYankee AA-1 · Floodwood, MNJul 25, 1969 · N5641L · Substantial damageNTSB OAK70F0086Accident · Minor injuriesClosedBell 47G3B1 · Stirling Cy, CAJul 23, 1969 · N8529F · Substantial damageNTSB SEA70F0050Accident · No injuriesClosedBeech A35 · Seattle, WAJul 22, 1969 · N3940 · Substantial damageNTSB MKC70F0096Accident · Serious injuriesClosedPiper PA-23 · Newton, IAJul 22, 1969 · N5246Y · DestroyedNTSB MIA70A0003Accident · 3 fatalClosedPiper PA-23 · Melbourne, FLJul 22, 1969 · N4453P · DestroyedNTSB CHI70F0103Accident · No injuriesClosedBaby Ace C · Plymouth, MIJul 22, 1969 · N85Y · Substantial damageNTSB OAK70F0211Accident · Serious injuriesClosedAero Comdr 500-B · Montague, CAJul 21, 1969 · N890CP · Substantial damageNTSB OAK70D0091Accident · Minor injuriesClosedCessna 150 · Tonopah, NVJul 21, 1969 · N60827 · Substantial damageNTSB MIA70F0143Accident · Serious injuriesClosedPiper PA-25 · Wrens, GAJul 21, 1969 · N4620Y · DestroyedNTSB NYC70D0101Accident · Minor injuriesClosedPiper PA-28 · Bondville, VTJul 20, 1969 · N545RF · DestroyedNTSB MKC70D0099Accident · No injuriesClosedPiper PA-12 · Pipestone, MNJul 20, 1969 · N4009M · Substantial damageNTSB LAX70D0124Accident · No injuriesClosedCessna 150 · Shafter, CAJul 20, 1969 · N3147J · Substantial damageNTSB NYC70D0105Accident · No injuriesClosedGlobe Swift GC-1B · Fitchburg, MAJul 19, 1969 · N2390B · DestroyedNTSB MIA70F0068Accident · Serious injuriesClosedNavion A · Kendall, FLJul 19, 1969 · N91605 · Substantial damageNTSB CHI70A0007Accident · Minor injuriesClosedCessna 310H · Green Bay, WIJul 19, 1969 · N1047Q · Substantial damageNTSB MIA70F0142Accident · No injuriesClosedCessna 150 · Twin City, GAJul 17, 1969 · N2229J · Substantial damageNTSB CHI70D0147Accident · No injuriesClosedCessna 177 · Swanton, OHJul 17, 1969 · N3206T · DestroyedNTSB CHI70D0198Accident · Minor injuriesClosedTaylorcraft BC1265 · Granger, INJul 16, 1969 · N29753 · Substantial damageNTSB NYC70A0010Accident · 3 fatalClosedDehavilland DHC-6 · Jamaica, NYJul 15, 1969 · N558MA · DestroyedNTSB MIA70F0073Accident · Minor injuriesClosedPiper PA-28 · Pascagoula, MSJul 15, 1969 · N8469R · Substantial damageNTSB FTW70F0101Accident · No injuriesClosedGrumman G-164A · Lonoke, ARJul 14, 1969 · N5287 · Substantial damageNTSB OAK70D0044Accident · No injuriesClosedLuscombe 8A · Modesto, CAJul 13, 1969 · N1344B · Substantial damageNTSB FTW70D0107Accident · No injuriesClosedMooney M20C · Andrews, TXJul 12, 1969 · N645OU · Substantial damageNTSB DIA70F0025Accident · No injuriesClosedCallair A-9 · Parkton, NCJul 12, 1969 · N7227V · Substantial damageNTSB FTW70D0115Accident · No injuriesClosedPiper PA-28 · Big Spring, TXJul 11, 1969 · N7451W · Substantial damageNTSB DEN70F0035Accident · Serious injuriesClosedCessna 140 · Gallup, NMJul 11, 1969 · N2393V · DestroyedNTSB MKC70F0092Accident · Minor injuriesClosedBell P-63C5 · Crystal, MNJul 10, 1969 · N62822 · Substantial damageNTSB MKC70F0045Accident · No injuriesClosedPiper PA-28 · Rapid City, SDJul 10, 1969 · N8927J · Substantial damageNTSB MKC70D0111Accident · No injuriesClosedPiper PA-32 · Eagle Grove, IAJul 10, 1969 · N3805W · Substantial damageNTSB CHI70F0112Accident · No injuriesClosedCessna 140 · Breman, INJul 8, 1969 · N4209N · Substantial damageNTSB NYC70F0083Accident · Serious injuriesClosedPiper PA-28 · Chenango Brdg, NYJul 6, 1969 · N6781W · DestroyedNTSB NYC70F0045Accident · No injuriesClosedPiper PA-18S · Lake Clear, NYJul 6, 1969 · N1559P · Substantial damageNTSB MIA70D0128Accident · No injuriesClosedPiper PA-28 · Fitzgerald, GAJul 6, 1969 · N1554J · Substantial damageNTSB LAX70F0057Accident · No injuriesClosedCessna 150F · Acton, CAJul 6, 1969 · N6850F · Substantial damageNTSB SEA70F0118Accident · Minor injuriesClosedPiper PA-28R · Condon, ORJul 5, 1969 · N3940T · Substantial damageNTSB NYC70F0035Accident · Minor injuriesClosedLuscombe 8A · Washington, NJJul 5, 1969 · N2687K · DestroyedNTSB MKC70D0065Accident · No injuriesClosedPiper PA-28 · Linn, MOJul 5, 1969 · N5582F · Substantial damageNTSB MIA70F0060Accident · Minor injuriesClosedAeronca 7FC · Weaver, ALJul 5, 1969 · N9891B · Substantial damageNTSB LAX70F0038Accident · Minor injuriesClosedAmer Avco AA-1 · El Cajon, CAJul 5, 1969 · N5674L · DestroyedNTSB DIA70F0031Accident · Serious injuriesClosedPiper PA-22 · Grantham, NCJul 5, 1969 · N3862P · DestroyedNTSB CHI70F0080Accident · Serious injuriesClosedPiper PA-28 · Chesaning, MIJul 5, 1969 · N4224J · DestroyedNTSB ANC70F0035Accident · No injuriesClosedCessna 180 · Anchorage, AKJul 5, 1969 · N2962A · Substantial damageNTSB SEA70D0015Accident · No injuriesClosedBell 47G-2 · Ontario, ORJul 4, 1969 · N9963Z · Substantial damageNTSB NYC70I0006Incident · No injuriesClosedVertol 107-II · Flushing, NYJul 4, 1969 · N6675D · No damageNTSB MKC70F0078Accident · Minor injuriesClosedVolmer VJ-22 · Excelsior, MNJul 4, 1969 · N9471 · Destroyed