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 CHI79FEP52Accident · Minor injuriesClosedDavis DA-3 · Burlington, WIJul 3, 1979 · N19MD · Substantial damageNTSB CHI79FER03Accident · Minor injuriesClosedCessna 182B · Osceola, WIJun 30, 1979 · N7228E · Substantial damageNTSB CHI79FEP47Accident · No injuriesClosedPiper PA-32R · Burlington, WIJun 6, 1979 · N1134Q · 2 aircraft · Substantial damageNTSB CHI79FEP44Accident · Serious injuriesClosedCessna T210N · St. Francis, WIApr 24, 1979 · N6485N · Substantial damageNTSB CHI79FDP35Accident · No injuriesClosedBeech G18S · Clintonville, WIMar 9, 1979 · N847U · Substantial damageNTSB CHI79DEP28Accident · No injuriesClosedBeech BE-19 · Milwaukee, WIFeb 10, 1979 · N1990L · Substantial damageNTSB CHI79FA027Accident · 1 fatalClosedBeech 58P · Waukesha, WIJan 27, 1979 · N9ZZ · DestroyedNTSB CHI79FEP16Accident · No injuriesClosedCessna 172K · Dodgeville, WIDec 22, 1978 · N78428 · Substantial damageNTSB CHI79FEP06Accident · Serious injuriesClosedGrumman AA1-A · Milwaukee, WINov 7, 1978 · N9424L · Substantial damageNTSB CHI78DEP62Accident · No injuriesClosedCessna 150 · Milwaukee, WIAug 20, 1978 · N3035V · Substantial damageNTSB CHI78DEP60Accident · No injuriesClosedCessna 150K · Kenosha, WIAug 18, 1978 · N6490G · Substantial damageNTSB CHI78FEP52Accident · 2 fatalClosedThorp T-18 · Oshkosh, WIJul 30, 1978 · N18FK · DestroyedNTSB CHI78FEP50Accident · No injuriesClosedCessna 172 · Tony, WIJul 28, 1978 · N3874R · Substantial damageNTSB CHI78FEP49Accident · Minor injuriesClosedCessna 172E · Pewaukee, WIJul 27, 1978 · N3845S · Substantial damageNTSB CHI78DEP48Accident · Minor injuriesClosedThorp T18 · Kenosha, WIJul 23, 1978 · N2911 · Substantial damageNTSB CHI78FA049Accident · 1 fatalClosedBellanca 17-30A · Rhinelander, WIJul 12, 1978 · N93627 · DestroyedNTSB CHI78DEP39Accident · No injuriesClosedMustang II · East Troy, WIJun 22, 1978 · N81M · Substantial damageNTSB CHI78FA042Accident · 1 fatalClosedCessna 150M · Waukesha, WIMay 28, 1978 · N9376U · DestroyedNTSB CHI78FEP36Accident · Serious injuriesClosedPiper PA-28 · Portage, WIMay 22, 1978 · N9436J · Substantial damageNTSB CHI78DEP16Accident · No injuriesClosedCessna 150 · Kenosha, WIFeb 9, 1978 · N10428 · Substantial damageNTSB CHI78FEP14Accident · Minor injuriesClosedBell 47-J · Wilmot, WIFeb 5, 1978 · N6780D · Substantial damageNTSB CHI78DER03Accident · No injuriesClosedCessna 150 · Superior, WIJan 10, 1978 · N6039K · Substantial damageNTSB CHI78FEP07Accident · No injuriesClosedCessna 177 · Madison, WIDec 26, 1977 · N2011Q · Substantial damageNTSB CHI78FA008Accident · 1 fatalClosedCessna 310P · Eagle River, WINov 7, 1977 · N5861M · DestroyedNTSB CHI78FA007Accident · 1 fatalClosedCessna 310Q · Watertown, WINov 6, 1977 · N7701Q · DestroyedNTSB CHI78FEP09Accident · Serious injuriesClosedPiper PA-31 · Manitowoc, WINov 3, 1977 · N9273Y · DestroyedNTSB CHI78FEP05Accident · No injuriesClosedPiper PA-24 · Green Bay, WIOct 19, 1977 · N5837P · Substantial damageNTSB CHI78FEP04Accident · Minor injuriesClosedCessna 172M · Elkhorn, WIOct 17, 1977 · N20011 · DestroyedNTSB CHI77DEP50Accident · No injuriesClosedSchweizer 1-26B · West Bend, WIAug 14, 1977 · N2780Z · Substantial damageNTSB CHI77FEP47Accident · Serious injuriesClosedCessna 172 · Lake Geneva, WIJul 31, 1977 · N7409A · Substantial damageNTSB CHI77DEP52Accident · No injuriesClosedBeech B-19 · Milwaukee, WIJul 30, 1977 · N1990L · Substantial damageNTSB CHI77FEP45Accident · No injuriesClosedBellanca 17-30A · Milwaukee, WIJul 18, 1977 · N39867 · Substantial damageNTSB CHI77FEP29Accident · Minor injuriesClosedCessna 172 · Janesville, WIMar 25, 1977 · N4352Q · Substantial damageNTSB CHI77FEP24Accident · Minor injuriesClosedBeech C45H · Milwaukee, WIMar 18, 1977 · N9929Z · DestroyedNTSB CHI77DEP20Accident · No injuriesClosedCessna 172 · Delavan, WIFeb 25, 1977 · N2113Y · Substantial damageNTSB CHI77DEP15Accident · No injuriesClosedPiper PA-28 · Pewaukee, WIJan 30, 1977 · N7504W · Substantial damageNTSB CHI77FEP14Accident · Minor injuriesClosedPiper PA-23 · Lone Rock, WIJan 21, 1977 · N1275P · DestroyedNTSB CHI77FEP12Accident · Serious injuriesClosedPiper PA-30A · Madison, WIDec 27, 1976 · N7184Y · DestroyedNTSB CHI77FA015Accident · 2 fatalClosedAeronca 7AC · Freedom, WIDec 15, 1976 · N2973E · Substantial damageNTSB CHI77DEP05Accident · Minor injuriesClosedCessna 140 · Brookfield, WINov 20, 1976 · N2168V · Substantial damageNTSB CHI77DEP03Accident · Minor injuriesClosedCessna 172 · Middleton, WINov 10, 1976 · N1582Y · Substantial damageNTSB CHI76DEP51Accident · Minor injuriesClosedCessna 150 · Boscobel, WISep 22, 1976 · N11106 · DestroyedNTSB CHI76DEP46Accident · Serious injuriesClosedCessna 172 · Dodgeville, WIAug 28, 1976 · N9626V · Substantial damageNTSB CHI76FEP42Accident · Minor injuriesClosedMooney M20E · Grafton, WIAug 13, 1976 · N5753Q · Substantial damageNTSB CHI76FEP39Accident · Minor injuriesClosedPiper PA-28 · Sturtevant, WIJul 24, 1976 · N44553 · Substantial damageNTSB CHI76FEP28Accident · No injuriesClosedPiper PA-28R · Pulaski, WIApr 20, 1976 · N4508J · Substantial damageNTSB CHI76AC052Accident · 1 fatalClosedBeech E18S · Burlington, WIMar 1, 1976 · N386 · DestroyedNTSB CHI76DEP23Accident · Minor injuriesClosedTaylorcraft BC12-D · Chambers Isle, WIJan 25, 1976 · N95927 · Substantial damageNTSB CHI76DEP21Accident · No injuriesClosedCessna 140 · Kenosha, WIJan 18, 1976 · N701F · Substantial damageNTSB CHI76AC044Accident · 1 fatalClosedPiper PA-28 · Beloit, WIJan 11, 1976 · N55577 · Destroyed