NTSB investigations, WI

228 cases where the phase of flight was Maneuvering.

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\" = 'WI' AND c.\"eventCountry\" = 'USA'
    AND c.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 450)
  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 CHI73AC092Accident · 1 fatalClosedCessna 172L · Crivitz, WIMay 26, 1973 · N7656G · DestroyedNTSB CHI73FEP10Accident · Serious injuriesClosedPiper PA-25 · Suring, WISep 12, 1972 · N4598Y · Substantial damageNTSB CHI73AC002Accident · 3 fatalClosedCessna 172K · Hales Corners, WIJul 4, 1972 · N79022 · DestroyedNTSB CHI72FEP42Accident · No injuriesClosedCessna 182P · Bloomer, WIMay 17, 1972 · N7412Q · Substantial damageNTSB CHI72DEP22Accident · No injuriesClosedCessna 150 · Kenosha, WINov 2, 1971 · N8229F · Substantial damageNTSB CHI72FEP07Accident · Serious injuriesClosedGrumman G-32A · Pickett, WIAug 7, 1971 · N7F · Substantial damageNTSB CHI72DEP02Accident · No injuriesClosedBoeing B75N1 · Blck Rvr Flls, WIJul 11, 1971 · N69808 · Substantial damageNTSB CHI71FEP53Accident · 1 fatalClosedPiper PA-18 · Ripon, WIJun 13, 1971 · N2366P · DestroyedNTSB CHI71DEP52Accident · No injuriesClosedPiper PA-25 · Random Lake, WIMay 28, 1971 · N4421Y · Substantial damageNTSB CHI71FDJ01Accident · Serious injuriesClosedMooney M20C · Union Grove, WIJul 2, 1970 · N9384V · DestroyedNTSB CHI70DDJ03Accident · No injuriesClosedCessna 170A · Prairie Du Sc, WIJan 3, 1970 · N5772C · Substantial damageNTSB CHI70F0200Accident · No injuriesClosedLuscombe 8A · So Superior, WIAug 31, 1969 · N39207 · Substantial damageNTSB CHI70D0095Accident · No injuriesClosedPiper PA-25 · Suring, WIJul 8, 1969 · N4598Y · Substantial damageNTSB CHI70F0273Accident · Minor injuriesClosedCessna 172D · Dodgeville, WIApr 9, 1969 · N2554U · Substantial damageNTSB CHI69A0040Accident · 4 fatalClosedCessna 172 · Platteville, WIOct 13, 1968 · N8624B · DestroyedNTSB CHI69F0233Accident · Minor injuriesClosedStinson 108-2 · Baraboo, WISep 7, 1968 · N333C · Substantial damageNTSB CHI69D0190Accident · No injuriesClosedPiper PA-18A · Hancock, WIAug 7, 1968 · N3708A · Substantial damageNTSB CHI69D0125Accident · Minor injuriesClosedBell 47G-2 · Wis.Dells, WIJul 6, 1968 · N2866B · Substantial damageNTSB CHI69A0005Accident · 2 fatalClosedLuscombe 8E · Union Grove, WIJul 6, 1968 · N1181B · DestroyedNTSB CHI68A0094Accident · 2 fatalClosedAeronca 7AC · Ladysmith, WIApr 21, 1968 · N85137 · DestroyedNTSB CHI68A0035Accident · 2 fatalClosedChampion 7ECA · Bloomer, WIOct 14, 1967 · N2820G · DestroyedNTSB CHI68F0177Accident · Serious injuriesClosedFlut-R-Bug SA6B · Baraboo, WIJun 13, 1967 · N7564U · DestroyedNTSB CHI67A0055Accident · 2 fatalClosedTaylorcraft BC12-D · Burlington, WIOct 23, 1966 · N39294 · DestroyedNTSB CHI67F0337Accident · No injuriesClosedChampion 7GCAA · Salem, WISep 17, 1966 · N9626S · Substantial damageNTSB MKC67F0118Accident · Serious injuriesClosedPitts Specl · Rice Lake, WIJul 3, 1966 · N7513U · DestroyedNTSB UNK65X0438Accident · Serious injuriesClosedAeronca 11AC · Boscobel, WIOct 25, 1964 · N86390 · Substantial damageNTSB UNK64X2610Accident · Serious injuriesClosedPiper PA18A · Waupun, WIJul 2, 1964 · N8078C · DestroyedNTSB UNK64X0251Accident · 2 fatalClosedCessna 182C · Horicon, WIJan 15, 1964 · N9023T · Destroyed