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 LAX68D0052Accident · Minor injuriesClosedTaylorcraft BC12-D · 29 Palms, CAJun 30, 1967 · N94999 · Substantial damageNTSB FTW67A0122Accident · No injuriesClosedHughes 269A · Tulsa, OKJun 30, 1967 · N8733F · Substantial damageNTSB ANC68F0045Accident · Serious injuriesClosedCessna 180 · Iliamna, AKJun 30, 1967 · N2608Y · DestroyedNTSB MKC68F0065Accident · No injuriesClosedPiper PA-22 · Council Grve, KSJun 29, 1967 · N1497A · Substantial damageNTSB MKC68F0074Accident · No injuriesClosedPiper PA-18 · Ashby, MNJun 28, 1967 · N8976D · Substantial damageNTSB MIA68F0055Accident · No injuriesClosedPiper PA-25 · Cairo, GAJun 28, 1967 · N7684Z · Substantial damageNTSB CHI68F0136Accident · Serious injuriesClosedCessna 180 · Hinsdale, ILJun 28, 1967 · N9228C · Substantial damageNTSB NYC68F0037Accident · Minor injuriesClosedMooney M20E · Millville, NJJun 26, 1967 · N79374 · Substantial damageNTSB NYC68F0032Accident · No injuriesClosedCessna 150 · Pittstown, NJJun 26, 1967 · N6608T · Substantial damageNTSB NYC68F0051Accident · No injuriesClosedCessna 172 · Snow Shoe, PAJun 25, 1967 · N6909X · DestroyedNTSB NYC68F0047Accident · Minor injuriesClosedStinson 10A · Shippenville, PAJun 25, 1967 · N36707 · Substantial damageNTSB MKC68F0033Accident · Serious injuriesClosedCessna T337B · Hutchinson, KSJun 25, 1967 · N2336S · DestroyedNTSB CHI68F0074Accident · Serious injuriesClosedLake LA-4 · Wilmington, OHJun 25, 1967 · N2018L · DestroyedNTSB DIA68D0042Accident · No injuriesClosedStits SA-3 · Staunton, VAJun 24, 1967 · N7798C · Substantial damageNTSB OAK68F0121Accident · No injuriesClosedBeech H35 · San Carlos, CAJun 22, 1967 · N4683D · Substantial damageNTSB MKC67A0085Accident · Serious injuriesClosedCessna 180 · Baudette, MNJun 22, 1967 · N8212V · Substantial damageNTSB SEA68D0036Accident · No injuriesClosedPiper PA-25 · Glenns Ferry, IDJun 19, 1967 · N7719Z · DestroyedNTSB FTW67F0019Accident · No injuriesClosedCessna 150 · Houma, LAJun 19, 1967 · N7150F · Substantial damageNTSB LAX67A0116Accident · No injuriesClosedHughes 269A · Boron, CAJun 18, 1967 · N8931F · Substantial damageNTSB DEN68D0017Accident · No injuriesClosedCessna 172 · Vernon, UTJun 18, 1967 · N7998B · Substantial damageNTSB MKC68F0051Accident · No injuriesClosedPiper PA-18 · Chester, NEJun 17, 1967 · N1308A · Substantial damageNTSB MKC68F0028Accident · Serious injuriesClosedCessna 150 · Canyon, MNJun 17, 1967 · N8693J · Substantial damageNTSB LAX68F0024Accident · Minor injuriesClosedGlobe GC-1B · Hntngtn Bch, CAJun 17, 1967 · N78025 · Substantial damageNTSB DIA68F0024Accident · Minor injuriesClosedCessna 150 · Gaithersburg, MDJun 16, 1967 · N3496J · Substantial damageNTSB MKC68D0055Accident · Minor injuriesClosedPiper PA-25 · Holdredge, NEJun 15, 1967 · N7381Z · Substantial damageNTSB CHI67A0133Accident · Serious injuriesClosedBeech A23 · West Chicago, ILJun 15, 1967 · N4721J · Substantial damageNTSB OAK68D0015Accident · Serious injuriesClosedPiper PA-22 · San Jose, CAJun 12, 1967 · N8885C · Substantial damageNTSB MKC67D0623Accident · No injuriesClosedMooney M20C · Sioux Falls, SDJun 12, 1967 · N6035Q · Substantial damageNTSB FTW67A0118Accident · No injuriesClosedSikorsky HRS-1 · Laplace, LAJun 12, 1967 · N4957S · Substantial damageNTSB OAK68D0060Accident · No injuriesClosedPiper PA-22 · Galt, CAJun 11, 1967 · N3459Z · Substantial damageNTSB MKC68D0013Accident · No injuriesClosedCallair S-1B1 · Lakin, KSJun 11, 1967 · N7260V · DestroyedNTSB FTW67F0650Accident · No injuriesClosedCessna 172 · Freemason Isle, LAJun 11, 1967 · N7983U · Substantial damageNTSB CHI68F0127Accident · Minor injuriesClosedCessna 172G · Polo, ILJun 11, 1967 · N3696L · Substantial damageNTSB ANC68F0017Accident · Minor injuriesClosedLuscombe 8A · Anchorage, AKJun 11, 1967 · N71921 · Substantial damageNTSB NYC68F0050Accident · No injuriesClosedPiper J3C-65 · Nedrow, NYJun 9, 1967 · N98499 · Substantial damageNTSB NYC68F0038Accident · Minor injuriesClosedCessna 150G · Shirley, NYJun 8, 1967 · N3176X · Substantial damageNTSB MIA68D0119Accident · No injuriesClosedMooney M20 · Birmingham, ALJun 8, 1967 · N79839 · Substantial damageNTSB FTW68F0078Accident · No injuriesClosedGrumman G-164 · Manvel, TXJun 8, 1967 · N603U · Substantial damageNTSB CHI68F0145Accident · Minor injuriesClosedAeronca 7AC · Strongsville, OHJun 8, 1967 · N83144 · Substantial damageNTSB NYC67A0173Accident · Minor injuriesClosedBell 47H-1 · Flushing, NYJun 7, 1967 · N2466B · Substantial damageNTSB MKC68F0159Accident · No injuriesClosedCessna 188 · Portis, KSJun 7, 1967 · N9786V · DestroyedNTSB LAX67D0443Accident · No injuriesClosedErcoupe 415C · Temecula, CAJun 6, 1967 · N2381H · Substantial damageNTSB DIA67F0109Accident · Minor injuriesClosedPiper PA-18A · Hendersonville, NCJun 6, 1967 · N9150D · Substantial damageNTSB CHI67F0673Accident · No injuriesClosedCessna 172 · Franklin, INJun 6, 1967 · N3920F · Substantial damageNTSB OAK67D0339Accident · No injuriesClosedCessna 150 · San Luis Ob, CAJun 2, 1967 · N6744F · Substantial damageNTSB MKC67A0076Accident · 1 fatalClosedPiper PA-24 · Seneca, KSJun 2, 1967 · N8177P · DestroyedNTSB DIA67I0021Incident · No injuriesClosedBoeing B-727 · Washington, DCJun 2, 1967 · N8133N · Minor damageNTSB CHI68D0047Accident · No injuriesClosedCessna 170 · Janesville, WIJun 2, 1967 · N4014V · DestroyedNTSB CAN F0440Accident · 1 fatalClosedAeronca 15AC · Blind River, CanadaJun 2, 1967 · N1454H · Substantial damageNTSB FTW67F0655Accident · Minor injuriesClosedCessna 172H · Bethany, OKJun 1, 1967 · N3783R · Substantial damage