NTSB investigations, CT

185 cases where the phase of flight was Enroute.

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 = 400)
  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 NYC71AN083Accident · Minor injuriesClosedHughes 269-B · New Haven, CTJan 23, 1971 · N9370F · DestroyedNTSB NYC71AN068Accident · 2 fatalClosedPiper PA-28 · Sharon, CTDec 6, 1970 · N8625W · DestroyedNTSB NYC71DFM17Accident · No injuriesClosedCessna 150J · Norwich, CTSep 26, 1970 · N60618 · Substantial damageNTSB NYC71AN038Accident · 2 fatalClosedCessna 337D · Lakeville, CTSep 18, 1970 · N337NH · DestroyedNTSB NYC71AN031Accident · 2 fatalClosedNardi FN-333 · Shelton, CTAug 30, 1970 · N917NS · DestroyedNTSB NYC71DFM07Accident · Minor injuriesClosedCessna 180 · Bridgeport, CTJul 22, 1970 · N3172C · DestroyedNTSB NYC70FFM10Accident · Minor injuriesClosedCessna 150H · Windham, CTApr 14, 1970 · N6450S · Substantial damageNTSB NYC70F0052Accident · Minor injuriesClosedCessna 177 · Middletown, CTJun 27, 1969 · N29677 · Substantial damageNTSB NYC69A0111Accident · 9 fatalClosedPiper PA-23 · Waterford, CTJun 14, 1969 · N4101P · 2 aircraft · DestroyedNTSB NYC69F0471Accident · Minor injuriesClosedCessna 177 · Unionville, CTMay 31, 1969 · N2296Y · Substantial damageNTSB NYC69F0385Accident · Serious injuriesClosedCessna U206 · East Granby, CTMar 20, 1969 · N8019Z · DestroyedNTSB NYC69D0330Accident · No injuriesClosedStitts Plaboy · Danbury, CTJan 26, 1969 · N339Y · Substantial damageNTSB NYC68F0506Accident · Serious injuriesClosedCessna 150 · Mansfield, CTJun 5, 1968 · N8112S · Substantial damageNTSB NYC68D0245Accident · No injuriesClosedMooney M20B · Fairfield, CTSep 30, 1967 · N74514 · Substantial damageNTSB NYC67A0170Accident · 1 fatalClosedPiper PA-18S · Westbrook, CTMay 28, 1967 · N3901Z · DestroyedNTSB NYC67F0566Accident · No injuriesClosedCessna 150G · Newtown, CTMar 19, 1967 · N3371J · Substantial damageNTSB NYC67A0092Accident · 2 fatalClosedPiper PA-18 · North Canaan, CTDec 11, 1966 · N3697Z · 2 aircraft · DestroyedNTSB NYC67F0392Accident · Minor injuriesClosedPiper PA-22 · Voluntown, CTNov 26, 1966 · N3603A · Substantial damageNTSB NYC67D0454Accident · No injuriesClosedN.American T-28A · Windsor Lock, CTNov 22, 1966 · N101TZ · Substantial damageNTSB NYC67F0347Accident · Minor injuriesClosedPiper PA-20 · Dayville, CTOct 29, 1966 · N7337K · Substantial damageNTSB NYC67A0054Accident · 1 fatalClosedPiper PA-32 · Hartford, CTSep 26, 1966 · N3625W · Substantial damageNTSB NYC67D0260Accident · No injuriesClosedLuscombe 8A · Enfield, CTSep 18, 1966 · N71411 · Substantial damageNTSB NYC67A0034Accident · Serious injuriesClosedPiper PA-32 · Glenville, CTAug 19, 1966 · N3711W · DestroyedNTSB NYC67A0035Accident · 1 fatalClosedCessna 172G · Middletown, CTAug 15, 1966 · N6003R · DestroyedNTSB NYC67F0145Accident · Minor injuriesClosedBeech A23 · Rocky Hill, CTJul 27, 1966 · N8870M · Substantial damageNTSB NYC66I0127Incident · No injuriesClosedLockheed L-1049 · Hartford, CTMay 6, 1966 · N6211C · No damageNTSB NYC65A0152Accident · 1 fatalClosedCessna 170 · E.Haddam, CTJun 20, 1965 · N3506D · Substantial damageNTSB NYC65F0193Accident · No injuriesClosedErcoupe 415C · East Haven, CTMay 6, 1965 · N93991 · Substantial damageNTSB NYC65A0095Accident · Serious injuriesClosedConvair CV-440 · Windsor Lock, CTJan 18, 1965 · N4406 · No damageNTSB UNK65X1211Accident · No injuriesClosedCessna 150B · Thomaston, CTJan 1, 1965 · N7325X · Substantial damageNTSB UNK65X0877Accident · No injuriesClosedPiper PA12 · Madison, CTNov 28, 1964 · N3355M · Substantial damageNTSB UNK64X3156Accident · Minor injuriesClosedErcoupe 415C · Wolcott, CTAug 1, 1964 · N99678 · DestroyedNTSB UNK64X2901Accident · No injuriesClosedPiper PA22 · East Haddam, CTJul 18, 1964 · N5695Z · DestroyedNTSB UNK64X1589Accident · No injuriesClosedAeronca 11AC · Enfield, CTMay 3, 1964 · N86327 · Substantial damageNTSB NYC62A0108Accident · 1 fatalClosedConvair 340 · Windsor Lock, CTOct 19, 1962 · N8415H · Minor damage