NTSB investigations, NH

91 cases where the phase of flight was Approach.

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\" = 'NH' AND c.\"eventCountry\" = 'USA'
    AND c.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 500)
  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 NYC81FA071Accident · 1 fatalClosedTaylorcraft BC-85 · Durham, NHJul 2, 1981 · N27486 · DestroyedNTSB NYC81FNC06Accident · No injuriesClosedBeech A23-24 · Manchester, NHNov 16, 1980 · N6129N · Substantial damageNTSB NYC80FNC23Accident · No injuriesClosedMooney M20C · Keene, NHMay 10, 1980 · N28R · Substantial damageNTSB NYC79FA093Accident · No injuriesClosedCessna 172 · Keene, NHSep 9, 1979 · N20613 · Substantial damageNTSB NYC79FA048Accident · 1 fatalClosedCessna 150H · Dalton, NHJun 3, 1979 · N7052S · DestroyedNTSB NYC79FNC23Accident · No injuriesClosedPiper PA-32R · New London, NHApr 17, 1979 · N1982H · Substantial damageNTSB NYC79DNC18Accident · No injuriesClosedPiper PA-28 · Antrim, NHFeb 16, 1979 · N8262 · Substantial damageNTSB NYC78FNC42Accident · Minor injuriesClosedCessna 182D · Hollis, NHJul 29, 1978 · N8810X · Substantial damageNTSB NYC78FNC41Accident · Minor injuriesClosedLake 4-200 · Meredith, NHJul 23, 1978 · N1049L · DestroyedNTSB NYC78FNC36Accident · Serious injuriesClosedCessna 172 · Wolfeboro, NHJul 1, 1978 · N3741S · DestroyedNTSB NYC78DNC18Accident · No injuriesClosedCessna 150 · Colebrook, NHFeb 28, 1978 · N6482G · Substantial damageNTSB NYC78DNC03Accident · No injuriesClosedPiper PA-28 · Chickville, NHOct 16, 1977 · N38155 · Substantial damageNTSB NYC77DNC32Accident · No injuriesClosedSchweizer SGS233 · Salem, NHMay 15, 1977 · N2533H · Substantial damageNTSB NYC77DNC10Accident · Minor injuriesClosedCessna 150 · Hampton, NHNov 13, 1976 · N45160 · Substantial damageNTSB NYC76FNC52Accident · Serious injuriesClosedPiper PA-28 · Keene, NHJun 3, 1976 · N95228 · DestroyedNTSB NYC76DNC15Accident · Serious injuriesClosedCessna 150F · Center Ossipee, NHSep 1, 1975 · N8411G · Substantial damageNTSB NYC75FNC18Accident · No injuriesClosedCessna 150L · Nashua, NHNov 9, 1974 · N11392 · Substantial damageNTSB NYC74DNC47Accident · No injuriesClosedAero Comdr 100 · Brookline, NHJun 13, 1974 · N3790X · Substantial damageNTSB NYC74FNC41Accident · Minor injuriesClosedCessna 305A · N Conway, NHMay 2, 1974 · N1836 · Substantial damageNTSB NYC74DNC39Accident · No injuriesClosedSchweizer SG1-26 · Hollis, NHApr 20, 1974 · N1109S · DestroyedNTSB NYC74AN050Accident · 1 fatalClosedPiper PA-23 · Hanover, NHNov 28, 1973 · N13978 · DestroyedNTSB NYC74DNC21Accident · No injuriesClosedSchweizer 126 · Franconia, NHSep 4, 1973 · N1118S · DestroyedNTSB NYC74FNC08Accident · No injuriesClosedAmer Avco AA1 · Rochester, NHJul 17, 1973 · N5773L · Substantial damageNTSB NYC73DNC48Accident · No injuriesClosedBeech 23 · Claremont, NHJan 14, 1973 · N2315Z · Substantial damageNTSB NYC73ANC46Accident · No injuriesClosedPiper PA-28 · Nashua, NHJan 11, 1973 · N15320 · Substantial damageNTSB NYC73FNC15Accident · No injuriesClosedPiper PA-30 · Manchester, NHJul 27, 1972 · N7846Y · Substantial damageNTSB NYC72DNC24Accident · Minor injuriesClosedPiper PA-28 · Londonderry, NHFeb 2, 1972 · N9337W · Substantial damageNTSB NYC72AN044Accident · 1 fatalClosedPiper PA-23 · Lyme Center, NHSep 20, 1971 · N3435P · DestroyedNTSB NYC71DFG07Accident · No injuriesClosedCessna 150G · Rochester, NHSep 20, 1970 · N8339J · Substantial damageNTSB NYC69A0025Accident · 3 fatalClosedCessna 180D · Ashland, NHAug 29, 1968 · N3170D · DestroyedNTSB NYC68D0441Accident · Minor injuriesClosedPiper PA-18 · Tuftonboro, NHApr 12, 1968 · N285HC · Substantial damageNTSB NYC68A0024Accident · 5 fatalClosedPiper PA-28 · Laconia, NHAug 6, 1967 · N4226J · 2 aircraft · DestroyedNTSB NYC68D0034Accident · No injuriesClosedCessna 150 · Nashua, NHJul 3, 1967 · N4156V · Substantial damageNTSB NYC67A0159Accident · 2 fatalClosedCessna 150G · Nashua, NHMay 14, 1967 · N3755J · DestroyedNTSB NYC67D042.Accident · No injuriesClosedPiper PA-22 · Jaffrey, NHDec 31, 1966 · N5805Z · Substantial damageNTSB NYC67D0311Accident · Minor injuriesClosedSchleicher KA-6 · Gorham, NHOct 8, 1966 · N6574D · DestroyedNTSB NYC65F0083Accident · No injuriesClosedCessna 150 · Stratford, NHMar 9, 1965 · N6567T · Substantial damageNTSB UNK65X1305Accident · Minor injuriesClosedPiper PA-22 · Nashua, NHJan 17, 1965 · N5939Z · 2 aircraft · Substantial damageNTSB UNK64X3360Accident · Minor injuriesClosedPiper PA28 · Plymouth, NHAug 13, 1964 · N7029W · DestroyedNTSB UNK64X2406Accident · 1 fatalClosedCessna 172 · Hampton, NHJun 20, 1964 · N4213F · DestroyedNTSB UNK64X1463Accident · No injuriesClosedPiper PA24 · Laconia, NHApr 26, 1964 · N6227P · Substantial damage