NTSB investigations, SC

193 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.\"eventStateOrRegion\" = 'SC' AND c.\"eventCountry\" = 'USA'
    AND 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 MIA74FLG13Accident · No injuriesClosedCessna 150F · Barnwell, SCNov 4, 1973 · N8297F · Substantial damageNTSB MIA73FLG35Accident · No injuriesClosedPiper PA-22 · Gaffney, SCMar 4, 1973 · N6018D · Substantial damageNTSB MIA73DLG34Accident · No injuriesClosedAmer Avco AA-1B · Aiken, SCFeb 28, 1973 · N6323L · Substantial damageNTSB MIA73FLG31Accident · No injuriesClosedErco 415-C · Inman, SCDec 30, 1972 · N99543 · Substantial damageNTSB MIA73AM031Accident · 4 fatalClosedBeech E18S · Sumter, SCSep 17, 1972 · N21DD · DestroyedNTSB MIA72DLG25Accident · Minor injuriesClosedCessna 172B · Greenville, SCFeb 19, 1972 · N6982X · Substantial damageNTSB MIA72FLG24Accident · Minor injuriesClosedPiper PA-28 · Andrews, SCJan 30, 1972 · N8044W · Substantial damageNTSB MIA72FLG16Accident · Serious injuriesClosedPiper PA-25 · Edisto Is, SCOct 28, 1971 · N162W · DestroyedNTSB MIA72FLG14Accident · No injuriesClosedPiper J3C-65 · Clio, SCOct 3, 1971 · N37949 · Substantial damageNTSB MIA72FLG11Accident · No injuriesClosedCessna 177 · Spartanburg, SCSep 16, 1971 · N3113T · Substantial damageNTSB MIA72FLG07Accident · No injuriesClosedPiper PA-28 · Greenville, SCAug 11, 1971 · N8820N · Substantial damageNTSB MIA71AM093Accident · 4 fatalClosedCessna 421A · Conestee, SCMay 2, 1971 · N3194K · DestroyedNTSB MIA71FLG27Accident · No injuriesClosedPiper J3C-65 · Pageland, SCApr 7, 1971 · N92308 · Substantial damageNTSB MIA71FLG21Accident · Minor injuriesClosedPiper PA-22 · Calhoun Falls, SCJan 3, 1971 · N802A · Substantial damageNTSB MIA71FLG14Accident · Serious injuriesClosedHelio Acft H-295 · Pelion, SCOct 2, 1970 · N6474V · Substantial damageNTSB MIA71FLG08Accident · No injuriesClosedPiper J3C-65 · Hartsville, SCAug 23, 1970 · N98105 · Substantial damageNTSB MIA71FLG04Accident · No injuriesClosedBeech 35 · Jackson, SCAug 13, 1970 · N3027V · Substantial damageNTSB MIA71FLG02Accident · No injuriesClosedBeech H35 · Cayce, SCJul 15, 1970 · N7920D · Substantial damageNTSB MIA70DLG01Accident · Minor injuriesClosedJay Tee RCB-1 · Johns Island, SCJan 31, 1970 · N8142 · Substantial damageNTSB MIA70F0155Accident · Serious injuriesClosedSturgeon A1 · Sumter, SCSep 14, 1969 · N2158 · DestroyedNTSB MIA70A0013Accident · 1 fatalClosedMooney M20E · Whitmire, SCAug 19, 1969 · N79353 · DestroyedNTSB MIA69F0474Accident · Serious injuriesClosedPiper PA-28 · Pelzer, SCApr 20, 1969 · N6307J · DestroyedNTSB MIA69A0068Accident · 1 fatalClosedCessna 172D · Summerton, SCApr 16, 1969 · N50001 · Substantial damageNTSB MIA69D0272Accident · No injuriesClosedCessna 170 · Pickens, SCOct 27, 1968 · N4117V · Substantial damageNTSB MIA69F0155Accident · No injuriesClosedHughes 269A · Cope, SCJul 24, 1968 · N8893F · Substantial damageNTSB MIA69D0136Accident · No injuriesClosedCallair A-9 · Estill, SCJul 22, 1968 · N7244V · DestroyedNTSB MIA68F0470Accident · No injuriesClosedBeech A23 · Landrum, SCMar 8, 1968 · N5997S · Substantial damageNTSB MIA69F0473Accident · No injuriesClosedBeech TC 45G · Cayce, SCFeb 17, 1968 · N199H · DestroyedNTSB MIA68D0468Accident · No injuriesClosedBeech 95-D55 · Orangeburg, SCFeb 5, 1968 · N7092N · Substantial damageNTSB MIA67A0120Accident · Serious injuriesClosedHughes 269B · Saluda, SCMay 13, 1967 · N9417F · Substantial damageNTSB MIA67F0261Accident · Minor injuriesClosedAeronca 65-LB · St-Stephens, SCSep 10, 1966 · N33701 · DestroyedNTSB MIA67A0004Accident · No injuriesClosedHughes 269B · Cope, SCJul 7, 1966 · N9410F · Substantial damageNTSB MIA66A0122Accident · No injuriesClosedBell 47G-5 · Mcbee, SCApr 21, 1966 · N8556F · Substantial damageNTSB MIA66D0521Accident · No injuriesClosedCessna 172 · Bishopville, SCApr 8, 1966 · N2005Y · Substantial damageNTSB MIA66F0441Accident · Minor injuriesClosedPiper J3C · Columbia, SCFeb 20, 1966 · N30551 · Substantial damageNTSB MIA66A0046Accident · 1 fatalClosedPiper PA-18 · Gaffney, SCOct 25, 1965 · N8794D · DestroyedNTSB UNK65X0226Accident · No injuriesClosedCurtiss C-46 · Charleston, SCOct 10, 1964 · N7923C · Substantial damageNTSB UNK64X4154Accident · Minor injuriesClosedAeronca 7AC · Newberry, SCSep 26, 1964 · N81939 · DestroyedNTSB UNK64X3209Accident · No injuriesClosedCessna 170B · Stuckey, SCAug 4, 1964 · N4588C · Substantial damageNTSB UNK64X2278Accident · No injuriesClosedPiper PA22 · Ridgeland, SCJun 14, 1964 · N1948P · Substantial damageNTSB UNK64X1213Accident · Serious injuriesClosedCessna 195 · York, SCApr 7, 1964 · N2185C · DestroyedNTSB UNK64X1105Accident · Serious injuriesClosedPiper PA22 · Union, SCMar 29, 1964 · N8824C · Substantial damageNTSB UNK64X0159Accident · Serious injuriesClosedPiper PA-24 · Hartsville, SCJan 5, 1964 · N5121P · Substantial damage