NTSB investigations, WI

424 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\" = 'WI' 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 CHI71AC012Accident · Serious injuriesClosedCessna 402 · Watertown, WIAug 8, 1970 · N8139F · DestroyedNTSB CHI71FDJ05Accident · Minor injuriesClosedPiper PA-28 · Green Bay, WIJul 21, 1970 · N6667J · Substantial damageNTSB CHI71DDJ04Accident · No injuriesClosedCessna 150 · Platteville, WIJul 10, 1970 · N3679J · Substantial damageNTSB CHI71DDJ02Accident · Minor injuriesClosedCessna 180F · Friendship, WIJul 3, 1970 · N2157Z · Substantial damageNTSB CHI70DDJ26Accident · No injuriesClosedCessna 180 · Lone Rock, WIJun 28, 1970 · N4930A · Substantial damageNTSB CHI70DDJ25Accident · No injuriesClosedCessna 182 · Milwaukee, WIJun 23, 1970 · N9958B · Substantial damageNTSB CHI70AC091Accident · No injuriesClosedCessna 150G · Burlington, WIJun 18, 1970 · N2213J · 2 aircraft · Minor damageNTSB CHI70DDJ22Accident · No injuriesClosedPiper PA-28R · Washington Is, WIJun 6, 1970 · N3057R · Substantial damageNTSB CHI70FDJ21Accident · No injuriesClosedPiper PA-22 · Mount Horeb, WIJun 3, 1970 · N8129D · Substantial damageNTSB CHI70FDM03Accident · Minor injuriesClosedChampion 7ECA · Webster, WIMay 30, 1970 · N11031 · Substantial damageNTSB CHI70AC080Accident · 1 fatalClosedTaylorcraft BC-12D · Mauston, WIMay 16, 1970 · N96260 · DestroyedNTSB CHI70DDJ14Accident · No injuriesClosedLuscombe 8A · Sheboygan Fls, WIMar 28, 1970 · N71170 · Substantial damageNTSB CHI70DDJ13Accident · No injuriesClosedLuscombe 8E · Wausau, WIMar 26, 1970 · N2040K · Substantial damageNTSB CHI70DDJ05Accident · No injuriesClosedCessna 150 · Waukesha, WIJan 18, 1970 · N7959E · Substantial damageNTSB MKC70A0018Accident · No injuriesClosedBeech E18S · Ashland, WIOct 12, 1969 · N3785B · Substantial damageNTSB CHI70F0276Accident · No injuriesClosedCessna 150 · River Falls, WIOct 5, 1969 · N7993E · Substantial damageNTSB CHI70F0342Accident · Serious injuriesClosedPiper J3C-65 · Niagara, WISep 23, 1969 · N42046 · DestroyedNTSB CHI70F0274Accident · No injuriesClosedPiper PA-24 · Middleton, WISep 17, 1969 · N8149P · Substantial damageNTSB CHI70D0298Accident · No injuriesClosedCessna 140 · Chilton, WIAug 24, 1969 · N1999V · Substantial damageNTSB CHI70F0135Accident · No injuriesClosedPiper PA-28 · Middleton, WIJul 27, 1969 · N6743J · Substantial damageNTSB CHI70D0129Accident · Minor injuriesClosedCessna 180 · Balsam Lake, WIJul 5, 1969 · N3606C · Substantial damageNTSB CHI70F0040Accident · Minor injuriesClosedJeanies Teenie · Lacrosse, WIJun 10, 1969 · N3188 · DestroyedNTSB CHI69A0095Accident · 1 fatalClosedPiper PA-20 · Milwaukee, WIMay 19, 1969 · N7604K · DestroyedNTSB MKC70D0021Accident · No injuriesClosedCessna 172 · Osceola, WIMay 17, 1969 · N29699 · Substantial damageNTSB CHI70D0029Accident · No injuriesClosedBeech 35-C33 · Burlington, WIMay 7, 1969 · N2027W · Substantial damageNTSB CHI69F0554Accident · No injuriesClosedCessna 150H · Dodgeville, WIMar 23, 1969 · N6620S · Substantial damageNTSB CHI69F0444Accident · No injuriesClosedCessna 150E · Pewaukee, WIJan 20, 1969 · N6221T · Substantial damageNTSB CHI69F0409Accident · Minor injuriesClosedPiper PA-28 · Sturtevant, WINov 24, 1968 · N4217T · Substantial damageNTSB CHI69F0317Accident · Minor injuriesClosedCessna 172D · Shawano, WIOct 13, 1968 · N2522Y · DestroyedNTSB CHI69D0294Accident · Minor injuriesClosedPiper PA-22 · Platteville, WIAug 9, 1968 · N4990Z · Substantial damageNTSB CHI69D0167Accident · Minor injuriesClosedCessna 150 · Franklin, WIJul 31, 1968 · N3906J · DestroyedNTSB CHI69D0189Accident · Minor injuriesClosedPiper PA-28 · Wonewoc, WIJul 14, 1968 · N6343R · Substantial damageNTSB CHI69D0113Accident · No injuriesClosedLuscombe 8A · Green Bay, WIJul 12, 1968 · N21996 · Substantial damageNTSB CHI69D0129Accident · No injuriesClosedPiper PA-22 · Neenah, WIJul 7, 1968 · N2244A · Substantial damageNTSB CHI69D0021Accident · Minor injuriesClosedPiper PA-28 · Juneau, WIApr 21, 1968 · N4283J · Substantial damageNTSB CHI68D0360Accident · No injuriesClosedPiper PA-22 · Platteville, WIFeb 24, 1968 · N3820P · Substantial damageNTSB CHI68D0584Accident · Minor injuriesClosedBeech A23 · Milwaukee, WIFeb 5, 1968 · N4733J · DestroyedNTSB CHI68A0053Accident · 7 fatalClosedBeech H18 · Madison, WIDec 10, 1967 · N390R · DestroyedNTSB CHI68F0598Accident · Serious injuriesClosedPiper PA-32 · Wauwatosa, WIDec 8, 1967 · N3437W · Substantial damageNTSB CHI68A0012Accident · 2 fatalClosedTaylorcraft BC12-D · Holcombe, WIJul 26, 1967 · N96507 · DestroyedNTSB CHI68D0324Accident · Minor injuriesClosedCurtis Scout · Waukesha, WIJul 25, 1967 · N2021 · Substantial damageNTSB CHI68D0375Accident · No injuriesClosedCessna 172 · Ft.Atkinson, WIJul 24, 1967 · N7248T · Substantial damageNTSB CHI67A0141Accident · No injuriesClosedBell 47G-2 · Wisc.Dells, WIJun 26, 1967 · N2866B · Substantial damageNTSB CHI68F0270Accident · No injuriesClosedCessna 182 · Osceola, WIJun 17, 1967 · N7228E · Substantial damageNTSB CHI68D0156Accident · No injuriesClosedCessna 182 · Baileys Hrbr, WIJun 15, 1967 · N9390X · Substantial damageNTSB CHI67D0661Accident · No injuriesClosedPiper PA-28 · Pewaukee, WIMay 12, 1967 · N6313W · Substantial damageNTSB CHI67F0430Accident · Minor injuriesClosedBeech C35 · Waukesha, WINov 12, 1966 · N5979C · Substantial damageNTSB CHI67D0347Accident · Minor injuriesClosedChampion 7FC · Elcho, WIOct 25, 1966 · N9862Y · Substantial damageNTSB CHI67A0016Accident · 1 fatalClosedCessna 150 · Racine, WIJul 27, 1966 · N3005J · DestroyedNTSB CHI67D0035Accident · Minor injuriesClosedLuscombe 8A · Montello, WIJul 2, 1966 · N71350 · Destroyed