NTSB investigations, WI

261 cases where the phase of flight was Takeoff.

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 = 300)
  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 CHI73DEP53Accident · No injuriesClosedCessna 150 · Tony, WIApr 24, 1973 · N8654G · Substantial damageNTSB CHI73FEP39Accident · No injuriesClosedMooney M20C · Middleton, WIFeb 8, 1973 · N6520U · Substantial damageNTSB CHI73DEP34Accident · No injuriesClosedCessna 310 · Lake Geneva, WIDec 15, 1972 · N6886T · Substantial damageNTSB CHI73FEP16Accident · No injuriesClosedTravel Air 4000 · Redgranite, WIAug 1, 1972 · N241 · Substantial damageNTSB CHI72FEP46Accident · No injuriesClosedLake LA-4 · Waukesha, WIJun 6, 1972 · N5034L · Substantial damageNTSB CHI72FEP43Accident · No injuriesClosedCessna 140 · Lafarge, WIMay 18, 1972 · N2353N · Substantial damageNTSB CHI72AC060Accident · No injuriesClosedBeech D18S · Madison, WIJan 20, 1972 · N80390 · Substantial damageNTSB CHI72DEP28Accident · No injuriesClosedPiper PA-28 · Waukesha, WIDec 31, 1971 · N2371T · Substantial damageNTSB CHI72DEP17Accident · No injuriesClosedMooney M20 · Hayward, WISep 28, 1971 · N5247B · Substantial damageNTSB CHI71FDM06Accident · No injuriesClosedPiper PA-28 · Cochrane, WIApr 22, 1971 · N5979W · Substantial damageNTSB CHI71DDJ42Accident · No injuriesClosedCessna 120 · Lake Geneva, WIFeb 7, 1971 · N72441 · Substantial damageNTSB CHI71DDJ38Accident · No injuriesClosedAero Comdr 100 · New Holstein, WIJan 21, 1971 · N3623X · Substantial damageNTSB CHI71FDJ03Accident · Minor injuriesClosedBeech C-18 · Waukesha, WIJul 10, 1970 · N2391C · Substantial damageNTSB CHI70DDM05Accident · Minor injuriesClosedCessna 150 · Baldwin, WIJun 7, 1970 · N8843S · Substantial damageNTSB CHI70DDJ18Accident · No injuriesClosedCessna 150H · Dodgeville, WIMay 21, 1970 · N6620S · Substantial damageNTSB CHI70D0278Accident · No injuriesClosedCessna 172 · Milwaukee, WIAug 30, 1969 · N46556 · Substantial damageNTSB CHI70F0319Accident · No injuriesClosedPiper PA-25 · Peshtigo, WIAug 25, 1969 · N7796Z · Substantial damageNTSB CHI69D0589Accident · No injuriesClosedCessna 170A · Crivitz, WIMar 2, 1969 · N1219D · Substantial damageNTSB CHI69D0486Accident · No injuriesClosedStinson 108-2 · Superior, WIFeb 13, 1969 · N962D · Substantial damageNTSB CHI69D0573Accident · No injuriesClosedLuscombe 8A · West Bend, WIFeb 9, 1969 · N72041 · Substantial damageNTSB CHI69D0430Accident · No injuriesClosedCessna 172 · Monroe, WIJan 2, 1969 · N8207B · Substantial damageNTSB CHI69D0331Accident · No injuriesClosedCessna 150 · Waukesha, WIOct 31, 1968 · N6422F · Substantial damageNTSB CHI69F0269Accident · No injuriesClosedPiper PA-28 · Juneau, WIAug 11, 1968 · N5858W · Substantial damageNTSB CHI69D0191Accident · No injuriesClosedAero Comdr 100 · Mattoon, WIJul 17, 1968 · N3884X · Substantial damageNTSB CHI68D0724Accident · No injuriesClosedPiper PA-24 · Milwaukee, WIApr 28, 1968 · N5078P · Substantial damageNTSB CHI68D0664Accident · No injuriesClosedCessna 150 · Pulaski, WIMar 21, 1968 · N2213J · Substantial damageNTSB CHI68D0411Accident · No injuriesClosedCessna 140 · Norwalk, WIOct 21, 1967 · N89033 · Substantial damageNTSB CHI68F0403Accident · Serious injuriesClosedTaylorcraft BL-65 · Portage, WIAug 27, 1967 · N24402 · DestroyedNTSB CHI68A0013Accident · Minor injuriesClosedBensen B8M · Footville, WIJul 29, 1967 · N9127 · DestroyedNTSB CHI68F0144Accident · No injuriesClosedHoward DGA-15 · Camp Lake, WIJul 2, 1967 · N74696 · Substantial damageNTSB CHI68F0269Accident · No injuriesClosedPiper PA-24 · Barron, WIJun 12, 1967 · N7763P · Substantial damageNTSB CHI68D0043Accident · No injuriesClosedMooney M20C · Tomah, WIJun 1, 1967 · N9207M · Substantial damageNTSB CHI68F0194Accident · No injuriesClosedChampion 7FC · Sturtevant, WIMar 30, 1967 · N8583E · Substantial damageNTSB MKC67D0479Accident · No injuriesClosedChampion 7GCBC · Osceola, WIFeb 27, 1967 · N5189T · Substantial damageNTSB CHI67D0491Accident · No injuriesClosedBeech 95 · Marshfield, WIJan 31, 1967 · N2734Y · Substantial damageNTSB CHI67D0436Accident · No injuriesClosedAeronca 15AC · Pt.Washington, WIDec 18, 1966 · N1401H · Substantial damageNTSB CHI67F0434Accident · No injuriesClosedCessna 210-5 · Waupun, WIDec 6, 1966 · N8047Z · Substantial damageNTSB MKC67D0311Accident · No injuriesClosedAeronca 7AC · Prairie Farm, WINov 20, 1966 · N85569 · Substantial damageNTSB CHI67F0354Accident · No injuriesClosedCessna 170B · Pewaukee, WINov 20, 1966 · N3120B · Substantial damageNTSB CHI67D0352Accident · No injuriesClosedCessna 150 · La Farge, WIOct 1, 1966 · N7725F · Substantial damageNTSB CHI67F0189Accident · No injuriesClosedBeech D18S · Janesville, WISep 8, 1966 · N156NA · Substantial damageNTSB CHI67D0120Accident · Minor injuriesClosedPiper PA-22 · Whitewater, WIJul 10, 1966 · N8494D · DestroyedNTSB CHI66F0748Accident · Minor injuriesClosedCessna 150 · Waubecka, WIMay 17, 1966 · N8540G · DestroyedNTSB CHI66F0685Accident · No injuriesClosedCessna 172E · Beaver Dam, WIApr 10, 1966 · N5361T · Substantial damageNTSB CHI66F0435Accident · Minor injuriesClosedCessna 172 · Lake Geneva, WISep 22, 1965 · N6287E · Substantial damageNTSB CHI66F0178Accident · No injuriesClosedCessna 140 · Milwaukee, WIAug 23, 1965 · N76465 · Substantial damageNTSB CHI66I0016Incident · No injuriesClosedBoeing B-720 · Milwaukee, WIAug 11, 1965 · N466US · Minor damageNTSB CHI66F0163Accident · No injuriesClosedNavion B · Portage, WIJun 23, 1965 · N5436K · Substantial damageNTSB CHI66D0162Accident · No injuriesClosedPiper PA-22 · Lake Geneva, WIJun 20, 1965 · N4568A · Substantial damageNTSB CHI66D0153Accident · No injuriesClosedCessna 182 · Franklin, WIJun 9, 1965 · N5568B · Substantial damage