NTSB investigations, CT

71 cases where the phase of flight was Takeoff.

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\" = 'CT' AND c.\"eventCountry\" = 'USA'
    AND c.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 300)
  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 NYC71DNE43Accident · Minor injuriesClosedCessna 172D · Danbury, CTJun 16, 1971 · N2733U · Substantial damageNTSB NYC71FNE35Accident · No injuriesClosedBeech 95 · Willimantic, CTMay 29, 1971 · N8226D · Substantial damageNTSB NYC71FNE30Accident · No injuriesClosedCessna 195 · Ansonia, CTApr 1, 1971 · N9330A · Substantial damageNTSB NYC69D0430Accident · No injuriesClosedCessna 150 · Southbury, CTApr 28, 1969 · N6703F · Substantial damageNTSB NYC69D0316Accident · Minor injuriesClosedChampion 7ECA · Simsbury, CTJan 12, 1969 · N8348V · Substantial damageNTSB NYC68D0479Accident · No injuriesClosedCessna 172 · East Windsor, CTApr 28, 1968 · N6882A · Substantial damageNTSB NYC68F0446Accident · No injuriesClosedPiper PA-23 · Windsor Lks, CTApr 14, 1968 · N4329Y · Substantial damageNTSB NYC68F0403Accident · No injuriesClosedCessna 177 · Windsor Lock, CTMar 6, 1968 · N2980X · Substantial damageNTSB NYC68D0327Accident · No injuriesClosedCessna 150 · Plainville, CTJan 6, 1968 · N3790J · Substantial damageNTSB NYC68F0280Accident · Serious injuriesClosedCessna 150F · Plainville, CTNov 5, 1967 · N6694F · Substantial damageNTSB NYC68F0159Accident · No injuriesClosedCessna 150E · Terryville, CTAug 20, 1967 · N4018U · Substantial damageNTSB NYC68D0056Accident · No injuriesClosedStearman PT-17 · Meriden, CTJul 1, 1967 · N1350M · Substantial damageNTSB NYC67D0643Accident · No injuriesClosedPiper PA-30 · Woodstock, CTApr 21, 1967 · N7455Y · Substantial damageNTSB NYC67D0575Accident · No injuriesClosedCessna 150 · Williamantic, CTMar 28, 1967 · N8896G · Substantial damageNTSB NYC66D0292Accident · No injuriesClosedCessna 150 · Hartford, CTOct 24, 1965 · N3545J · Substantial damageNTSB NYC66D0301Accident · No injuriesClosedMooney M20C · Danbury, CTOct 12, 1965 · N5834Q · Substantial damageNTSB NYC66D0294Accident · No injuriesClosedPiper PA-28 · Hartford, CTOct 10, 1965 · N6937W · Substantial damageNTSB NYC66D0073Accident · No injuriesClosedCessna 170B · Hartford, CTJun 30, 1965 · N113E · Substantial damageNTSB NYC65F0056Accident · No injuriesClosedBeechraft 35-33 · Bethany, CTMar 7, 1965 · N928T · Substantial damageNTSB UNK64X2915Accident · No injuriesClosedCessna 172 · Waterford, CTJul 19, 1964 · N6434B · DestroyedNTSB UNK64X1352Accident · No injuriesClosedCessna 172 · Woodstock, CTApr 17, 1964 · N7660X · Substantial damage