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 NYC79FNE1SAccident · No injuriesClosedCessna 182 · Ellington, CTMay 28, 1979 · N2486G · Substantial damageNTSB NYC79FNE04Accident · No injuriesClosedCessna 150 · Wethersfield, CTJan 8, 1979 · N11347 · Substantial damageNTSB NYC79FA015Accident · 3 fatalClosedBeech C55 · Enfield, CTDec 7, 1978 · N82V · DestroyedNTSB NYC78FNE30Accident · No injuriesClosedCessna 152 · East Hampton, CTAug 6, 1978 · N714UP · Substantial damageNTSB NYC78FNE23Accident · No injuriesClosedAeronca 11AC · Fairfield, CTJun 26, 1978 · N86013 · Substantial damageNTSB NYC78FA063Accident · 1 fatalClosedBeech 95 · Waterford, CTJun 8, 1978 · N2714Y · Substantial damageNTSB NYC78FA027Accident · 1 fatalClosedPiper PA-32R · Danbury, CTFeb 10, 1978 · N47574 · DestroyedNTSB NYC78FNE03Accident · Serious injuriesClosedMooney M20 · New Haven, CTDec 26, 1977 · N6806V · Substantial damageNTSB NYC77FA090Accident · 2 fatalClosedPiper PA-28R · Greenwich, CTAug 31, 1977 · N3960X · DestroyedNTSB NYC77FA078Accident · 2 fatalClosedBeagle B.206S · Westport, CTAug 11, 1977 · N850EX · DestroyedNTSB NYC77FNE32Accident · Minor injuriesClosedCessna 150 · Enfield, CTJul 25, 1977 · N1555Q · Substantial damageNTSB NYC77FNE27Accident · Minor injuriesClosedMooney M20C · Portland, CTJun 26, 1977 · N6928N · Substantial damageNTSB NYC77FNE24Accident · No injuriesClosedCessna 120 · Woodbridge, CTJun 26, 1977 · N1825V · Substantial damageNTSB NYC77FNE20Accident · No injuriesClosedPiper PA-28 · Milford, CTMay 22, 1977 · N9937W · Substantial damageNTSB NYC77FA051Accident · No injuriesClosedAero Comdr 500B · Bridgeport, CTApr 12, 1977 · N471A · 2 aircraft · Substantial damageNTSB NYC77FNE05Accident · Serious injuriesClosedCessna 150 · Meriden, CTNov 6, 1976 · N63562 · DestroyedNTSB NYC76IA006Incident · No injuriesClosedPiper PA-23 · Windsor Locks, CTOct 30, 1976 · N5670Y · No damageNTSB NYC77FNE03Accident · Minor injuriesClosedCessna 210E · Burlington, CTOct 26, 1976 · N4985U · Substantial damageNTSB NYC76FNE30Accident · 1 fatalClosedSchweizer SGS126 · Watertown, CTJul 5, 1976 · N2646H · DestroyedNTSB NYC76AN073Accident · 2 fatalClosedCessna 150 · Bridgeport, CTFeb 4, 1976 · N248Q · DestroyedNTSB NYC76FNE10Accident · Minor injuriesClosedCessna 172F · Stratford, CTNov 28, 1975 · N8456U · Substantial damageNTSB NYC76AN023Accident · 1 fatalClosedCessna 172M · Stamford, CTAug 17, 1975 · N22234 · DestroyedNTSB NYC75FNE38Accident · Minor injuriesClosedBeech B19 · Barkhamsted, CTMay 29, 1975 · N73AK · DestroyedNTSB NYC75AN112Accident · 2 fatalClosedBeech A45 · Southport, CTMay 29, 1975 · N1061X · DestroyedNTSB NYC75AN095Accident · 2 fatalClosedBell 206B · Shelton, CTMar 25, 1975 · N83176 · DestroyedNTSB NYC75AN082Accident · 1 fatalClosedBell 206A · Milford, CTFeb 1, 1975 · N4726R · DestroyedNTSB NYC75AN079Accident · 1 fatalClosedPiper PA-23 · Stratford, CTJan 10, 1975 · N4322Y · Substantial damageNTSB NYC75FNE21Accident · No injuriesClosedCessna 172I · Oxford, CTDec 15, 1974 · N46096 · Substantial damageNTSB NYC75FNE20Accident · Minor injuriesClosedCessna 172 · Greenwich, CTNov 24, 1974 · N7425T · Substantial damageNTSB NYC75FNE17Accident · 1 fatalClosedSchriever Trpedo · Chesire, CTOct 19, 1974 · N73697 · DestroyedNTSB NYC75AN030Accident · 3 fatalClosedAmer Avco AA-5 · Roxbury, CTSep 1, 1974 · N7114L · DestroyedNTSB NYC73AN002Accident · 3 fatalClosedPiper PA-28 · Tolland, CTJul 4, 1973 · N4470T · DestroyedNTSB NYC73FNE34Accident · No injuriesClosedPiper PA-28 · Danbury, CTJun 10, 1973 · N9591W · DestroyedNTSB NYC73FNE32Accident · Minor injuriesClosedErco 415-D · Waterford, CTApr 30, 1973 · N99416 · Substantial damageNTSB NYC73ANE15Accident · Serious injuriesClosedCessna 182L · Easton, CTJan 6, 1973 · N3356R · DestroyedNTSB NYC73FNE07Accident · Serious injuriesClosedCessna 150J · Waterford, CTAug 25, 1972 · N3045J · DestroyedNTSB NYC73AN022Accident · 1 fatalClosedRobertson 182K · Orange, CTAug 2, 1972 · N2676R · DestroyedNTSB NYC73AN013Accident · Serious injuriesClosedBoeing 707 · Windsor Lock, CTJul 18, 1972 · N8705T · No damageNTSB NYC73FNE35Accident · Serious injuriesClosedCessna 150B · Union, CTMay 6, 1972 · N7348X · DestroyedNTSB NYC72AN128Accident · 1 fatalClosedCessna 185 · Tariffville, CTApr 13, 1972 · N5854T · DestroyedNTSB NYC72FNE28Accident · Serious injuriesClosedCessna 182H · Bloomfield, CTFeb 28, 1972 · N2337X · Substantial damageNTSB NYC72FNC22Accident · No injuriesClosedCessna 150 · Mid Haddam, CTJan 27, 1972 · N3991J · Substantial damageNTSB NYC72FNE17Accident · No injuriesClosedCommonwlth 185 · Middlebury, CTDec 12, 1971 · N92914 · Substantial damageNTSB NYC72AN081Accident · 3 fatalClosedCessna 170 · Woodstock, CTDec 12, 1971 · N4016V · Substantial damageNTSB NYC72AN040Accident · No injuriesClosedBell 206A · Westport, CTSep 15, 1971 · N1409W · Substantial damageNTSB NYC72FNE03Accident · No injuriesClosedRotorway Scorpn · Westbrook, CTAug 1, 1971 · N8851 · Substantial damageNTSB NYC71IN115Incident · No injuriesClosedDouglas DC-9 · New Haven, CTJun 1, 1971 · N8924E · Minor damageNTSB NYC71DNE36Accident · Minor injuriesClosedCessna 172K · Willimantic, CTMay 29, 1971 · N7377G · DestroyedNTSB NYC71FNE34Accident · Minor injuriesClosedFlybaby 1A · Oxford, CTMay 20, 1971 · N3314 · DestroyedNTSB NYC71FNE33Accident · No injuriesClosedKitalina AFB · Waterford, CTMay 20, 1971 · N5577 · Substantial damage