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 SEA67D0010Accident · Minor injuriesClosedPiper PA-12 · La Grande, ORMay 20, 1966 · N3744M · DestroyedNTSB NYC66D0699Accident · No injuriesClosedCessna 140 · Inlet, NYMay 20, 1966 · N72954 · Substantial damageNTSB MKC66F0582Accident · Minor injuriesClosedErco 415-C · Harlan, IAMay 20, 1966 · N99695 · DestroyedNTSB FTW66F0674Accident · No injuriesClosedPiper J3C-65 · Crowville, LAMay 19, 1966 · N26783 · Substantial damageNTSB SEA67F0040Accident · No injuriesClosedCessna 172C · Maupin, ORMay 17, 1966 · N8467X · Substantial damageNTSB MKC66A0059Accident · 3 fatalClosedAeronca 15AC · Circle Pines, MNMay 17, 1966 · N1451H · DestroyedNTSB FTW66A0105Accident · No injuriesClosedBell Tomcat · Broken Bow, OKMay 16, 1966 · N9168R · Substantial damageNTSB DEN66D0315Accident · No injuriesClosedHelio Acft H395 · Cliff, NMMay 15, 1966 · N4200B · Substantial damageNTSB FTW66A0102Accident · 1 fatalClosedAeronca 7AC · Freer, TXMay 14, 1966 · N83435 · DestroyedNTSB MIA66A0131Accident · 1 fatalClosedCessna 150 · Lagrange, GAMay 13, 1966 · N3513J · DestroyedNTSB DEN66D0311Accident · No injuriesClosedCessna 140 · Watkins, COMay 13, 1966 · N1667V · Substantial damageNTSB MKC66F0592Accident · No injuriesClosedGrumman G-164 · Charleston, MOMay 12, 1966 · N608U · Substantial damageNTSB MKC66F0562Accident · No injuriesClosedCessna 150 · Kansas City, KSMay 12, 1966 · N6725F · Substantial damageNTSB DEN66D0317Accident · No injuriesClosedPiper PA-12 · Roswell, NMMay 12, 1966 · N3780M · Substantial damageNTSB OAK66F0298Accident · Serious injuriesClosedCessna 336 · Concord, CAMay 11, 1966 · N3809U · DestroyedNTSB NYC66D0659Accident · No injuriesClosedAeronca 7AC · Derry, NHMay 10, 1966 · N81745 · Substantial damageNTSB MIA66I0590Accident · 1 fatalClosedAeronca O-58 · Sylvania, GAMay 10, 1966 · N49769 · DestroyedNTSB SEA67F0041Accident · Serious injuriesClosedCessna 172 · Ocean Shores, WAMay 8, 1966 · N5949A · DestroyedNTSB MKC66F0549Accident · No injuriesClosedPiper PA-20 · Minnesota Lk, MNMay 8, 1966 · N6893K · Substantial damageNTSB MIA66F0546Accident · No injuriesClosedPiper PA-22 · Trinity, NCMay 8, 1966 · N1421A · Substantial damageNTSB MIA66F0543Accident · Serious injuriesClosedCessna 170B · Greenville, TNMay 8, 1966 · N3118A · Substantial damageNTSB FTW66F0663Accident · No injuriesClosedPiper PA-32 · Littlefield, TXMay 8, 1966 · N3238W · Substantial damageNTSB SEA66F0291Accident · No injuriesClosedPiper PA-32 · Anaconda, MTMay 7, 1966 · N3436W · Substantial damageNTSB NYC66D0682Accident · Minor injuriesClosedPiper PA-22 · Salem, NJMay 7, 1966 · N7716D · Substantial damageNTSB MKC66F0551Accident · Minor injuriesClosedPiper PA-18 · Martin, SDMay 7, 1966 · N3794Z · Substantial damageNTSB MIA66F0565Accident · No injuriesClosedLuscombe 8A · Leesburg, FLMay 7, 1966 · N71776 · Substantial damageNTSB DEN66F0321Accident · No injuriesClosedStinson 108 · Mesa, AZMay 7, 1966 · N97919 · Substantial damageNTSB OAK66F0290Accident · No injuriesClosedCessna 150 · Fresno, CAMay 6, 1966 · N8093S · Substantial damageNTSB NYC66F0661Accident · Serious injuriesClosedPiper PA-28 · E.Williamson, NYMay 6, 1966 · N5032W · DestroyedNTSB FTW67F0002Accident · Minor injuriesClosedCessna 150 · Arren, ARMay 6, 1966 · N7170X · Substantial damageNTSB MIA66A0128Accident · 2 fatalClosedCessna 172E · Clearwater, FLMay 5, 1966 · N3907S · DestroyedNTSB MIA66A0129Accident · 2 fatalClosedPiper PA-18 · Atmore, ALMay 3, 1966 · N1119C · DestroyedNTSB NYC66D0680Accident · No injuriesClosedPiper J3C · New Hamburg, NYMay 2, 1966 · N38527 · Substantial damageNTSB NYC66A0667Accident · Serious injuriesClosedPiper PA-28 · Willimantic, CTMay 2, 1966 · N4600R · Substantial damageNTSB SEA66D0263Accident · Minor injuriesClosedPiper PA-22 · Union, IDMay 1, 1966 · N5830P · Substantial damageNTSB MKC66D0566Accident · No injuriesClosedPiper PA-28 · Cresco, IAMay 1, 1966 · N8733M · Substantial damageNTSB FTW66F0649Accident · No injuriesClosedCessna 175C · Rosharon, TXMay 1, 1966 · N8330T · Substantial damageNTSB CHI66F0760Accident · No injuriesClosedStinson 108-1 · Walbridge, OHMay 1, 1966 · N97510 · Substantial damageNTSB NYC66F0704Accident · Minor injuriesClosedPiper PA-22 · Tewksbury, MAApr 30, 1966 · N8862D · Substantial damageNTSB MKC67D0031Accident · No injuriesClosedPiper PA-11 · Stratford, IAApr 26, 1966 · N4688M · Substantial damageNTSB LAX66F0326Accident · Serious injuriesClosedPiper PA-12 · Iverside, CAApr 26, 1966 · N3786M · Substantial damageNTSB CHI66A0086Accident · 3 fatalClosedPiper PA-24 · Moddle Bass, OHApr 26, 1966 · N5288P · DestroyedNTSB NYC66F0626Accident · No injuriesClosedAeronca 65-TAC · Moneta, VAApr 23, 1966 · N36570 · DestroyedNTSB MIA67F0550Accident · No injuriesClosedErcoupe 415-D · Crystal River, FLApr 23, 1966 · N99176 · Substantial damageNTSB MIA66D0537Accident · No injuriesClosedPiper PA-28 · Tampa, FLApr 23, 1966 · N6325W · Substantial damageNTSB DEN66F0305Accident · Minor injuriesClosedLuscombe 8C · Evanston, WYApr 23, 1966 · N39094 · Substantial damageNTSB DEN66D0320Accident · Minor injuriesClosedPiper PA-28 · Gila Bend, AZApr 22, 1966 · N8580W · Substantial damageNTSB ANC67A0038Accident · 2 fatalClosedCessna 172F · Anchorage, AKApr 22, 1966 · N8690U · DestroyedNTSB OAK66F0266Accident · No injuriesClosedGrumman G-164 · Richvale, CAApr 21, 1966 · N617Y · Substantial damageNTSB MIA66A0122Accident · No injuriesClosedBell 47G-5 · Mcbee, SCApr 21, 1966 · N8556F · Substantial damage