Final NTSB investigations

1,264 cases where the phase of flight was Unknown.

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.\"completionStatus\" = 'Completed'
    AND c.\"mKey\" IN (SELECT case_mkey FROM case_phases WHERE main = 990)
  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 WPR20LA190Accident · No injuriesClass 3 · LimitedFinal reportSchweizer 269C · North Las Vegas, NVA total loss of engine power for reasons that could not be determined based on the available information.Jun 23, 2020 · N202CC · Substantial damageNTSB CEN20FA098Accident · 1 fatalClass 3 · FieldFinal reportInnovator Mosquitto · North Manchester, INThe pilot’s loss of control for reasons that could not be determined based on the available information.Feb 23, 2020 · N840GS · Substantial damageNTSB WPR20FAMS2Accident · 1 fatalClass 3 · Missing aircraftFinal reportLuscombe 8 · Pacific Ocean, ORUndetermined because the airplane and pilot were not located.Feb 12, 2020 · N77875 · DestroyedNTSB WPR20FAMS1Accident · 2 fatalClass 3 · Missing aircraftFinal reportPiper PA28R · Shelter Cove, CAUndetermined due to lack of available evidence.Oct 25, 2019 · N4958J · DestroyedNTSB WPR20LA014Accident · Serious injuriesClass 3 · LimitedFinal reportVans RV 6 · Spanaway, WAA total loss of engine power during the initial climb for reasons that could not be determined.Oct 23, 2019 · N615RJ · Substantial damageNTSB CEN20LA021Accident · 1 fatalClass 3 · LimitedFinal reportPiper PA23 · (Gulf of Mexico)The airplane’s descent into the ocean for reasons that could not be determined.Oct 17, 2019 · N778PA · DestroyedNTSB CEN19FA248Accident · 1 fatalClass 3 · FieldFinal reportCessna 310 · De Queen, ARA loss of control for reasons that could not be determined based on the available information.Aug 4, 2019 · N310QA · Substantial damageNTSB GAA19CA282Accident · No injuriesData collectionFinal reportCessna 172 · Edwards Air Force Base, CAAn undetermined event, which resulted in substantial damage to the airplane for reasons that could not be determined based on the available evidence.Apr 28, 2019 · N106ED · Substantial damageNTSB WPR19LA120Accident · No injuriesClass 3 · LimitedFinal reportPiper PA 18 225 DD · McCall, IDA total loss of engine power for reasons that could not be determined based on the available information.Apr 23, 2019 · N727WW · Substantial damageNTSB WPR19LA039Accident · No injuriesClass 3 · LimitedFinal reportBeech 36 · Los Banos, CAA partial loss of engine power for reasons that could not be determined based on the available information.Dec 6, 2018 · N361TC · Substantial damageNTSB CEN19FA032Accident · 1 fatalField investigationFinal reportMooney M20C · Santa Fe, NMCollision with terrain for reasons that could not be determined based on the available information. Contributing to the accident was the pilot's poor preflight planning and poor decision to depart on the accident flight.Nov 27, 2018 · N113TA · DestroyedNTSB ANC18FA071Accident · 2 fatalField investigationFinal reportPiper PA-12-150 · Healy, AKAn impact with terrain for reasons that could not be determined based on the available information.Sep 23, 2018 · N7511H · Substantial damageNTSB WPR18FA228Accident · 2 fatalClass 3 · FieldFinal reportPietenpol Aircamper · Camp Verde, AZLoss of control for reasons that could not be determined because postaccident examination of the airframe and engine revealed no mechanical anomalies that would have precluded normal operation.Aug 18, 2018 · N88HK · Substantial damageNTSB CEN18LA338Accident · Serious injuriesLimited investigationFinal reportLazair II · Racine, OHThe impact with terrain for reasons that could not be determined based on the lack of evidence.Aug 15, 2018 · UNREG · DestroyedNTSB WPR18FA143Accident · 1 fatalField investigationFinal reportI.C.A.-Brasov IS 29D · Avenal, CAThe pilot's improper installation of the wings onto the glider, which resulted in an in-flight wing separation. Contributing to the accident were the pilot's limited familiarity with the design and a lack of reliable assembly guidance.May 19, 2018 · N38ES · DestroyedNTSB CEN18FA083Accident · 1 fatalField investigationFinal reportSkwira Matthew J W-10 Tailwind · Sauk Centre, MNImpact with terrain for reasons that could not be determined based on the available information.Jan 23, 2018 · N251WT · Substantial damageNTSB ERA18LA039Accident · 2 fatalLimited investigationFinal reportKevin Leue Sparrow Hawk · Hernando, MSImpact with trees and terrain for reasons that could not be determined based on available evidence.Dec 2, 2017 · N481ZK · DestroyedNTSB GAA18CA020Accident · No injuriesData collectionFinal reportCessna 150 · Pottsville, PAThe failure of the right rudder cable and subsequent loss of directional control during landing.Oct 22, 2017 · N22092 · Substantial damageNTSB CEN17FA132Accident · 1 fatalField investigationFinal reportCessna 210B · Opal, SDThe pilot's loss of airplane control for reasons that could not be determined due to a lack of evidence.Mar 16, 2017 · N9581X · DestroyedNTSB CEN17CA140Accident · No injuriesData collectionFinal reportCessna 172R · Cahokia, ILA hard landing for reasons that could not be determined based on the available evidence.Feb 23, 2017 · N51827 · Substantial damageNTSB WPR17FAMS2Accident · 3 fatalMissing aircraftFinal reportCessna 172M · Open water, HIUndetermined because the airplane was not located.Dec 31, 2016 · N174LL · DestroyedNTSB WPR17LA034Accident · No injuriesClass 3 · LimitedFinal reportBell OH 58A · Sacramento, CAThe failure of the vertical stabilizer due to fatigue.Dec 4, 2016 · N916PD · Substantial damageNTSB CEN16FA344Accident · 2 fatalField investigationFinal reportAves Douglas James RV 6 · Viborg, SDThe pilot's failure to maintain airplane control, which resulted in the airplane exceeding its critical angle of attack, an aerodynamic stall, and subsequent flat spin into terrain.Sep 2, 2016 · N676DT · DestroyedNTSB ERA16LA201Accident · 1 fatalLimited investigationFinal reportCriquet Aviation Bogota Columb Storch FI-156 · Fordsville, KYThe steep descent to ground impact for reasons that could not be determined based on the available evidence.May 30, 2016 · N429BB · DestroyedNTSB WPR16FAMS1Accident · 2 fatalMissing aircraftFinal reportCessna 172N · Avalon, CAUndetermined because the airplane was not located.May 28, 2016 · N739ZE · DestroyedNTSB ERA16FA189Accident · 1 fatalField investigationFinal reportCessna 210 · Wantage, NJA collision with terrain for reasons that could not be determined as the investigation did not reveal any postaccident anomalies with the airplane or pilot.May 17, 2016 · N6450N · Substantial damageNTSB GAA15CA161Accident · No injuriesData collectionFinal reportCessna 172S · Galesburg, ILFirewall damage reported by maintenance personnel that occurred during an unknown phase of flight, because the accident flight and pilot could not be determined.Jun 10, 2015 · N740SP · Substantial damageNTSB ANC15FA032Accident · 1 fatalField investigationFinal reportCessna 207 · Bethel, AKThe pilot's failure to maintain altitude, which resulted in collision with the terrain. Contributing to the accident was the pilot's impairment from carbon monoxide exposure in flight. The source of the carbon monoxide could not be determined because the…May 30, 2015 · N1653U · Substantial damageNTSB WPR15FAMS1Accident · 1 fatalMissing aircraftFinal reportCessna 172S · Davenport, CAUndetermined because the airplane was not located.May 8, 2015 · N191TGNTSB CEN15LA185Accident · No injuriesLimited investigationFinal reportGiackino Donald W Rotorway 162F · Marquette, MIThe loss of helicopter control for reasons that could not be determined based on the available evidence.Mar 28, 2015 · N556DG · Substantial damageNTSB GAA15CA018Accident · No injuriesData collectionFinal reportCessna 172S · Camarillo, CAThe pilot involved and the phase of flight when the damage occurred were not determined.Mar 23, 2015 · N2104H · Substantial damageNTSB ERA15LA001Accident · 1 fatalLimited investigationFinal reportAir Command 582 · Monongahela, PAThe amateur-built gyroplane’s collision with terrain for reasons that could not be determined based on the available information. Contributing to the accident was the pilot’s decision to conduct the flight without obtaining additional training.Oct 1, 2014 · UNREG · Substantial damageNTSB ERA14FA455Accident · 2 fatalField investigationFinal reportPiper J3C-65 · Fort Gaines, GAThe pilot’s failure to maintain airplane control during flight, which resulted in a collision with a river.Sep 16, 2014 · N95480 · Substantial damageNTSB CEN14FA468Accident · 2 fatalField investigationFinal reportSchafer Rick W Foxtrot 4 · Abilene, TXThe pilot's decision to attempt a steep turn to return to the airport after reporting an emergency. The reason for the emergency could not be determined because postaccident examination of the airframe and engine did not reveal any mechanical anomalies that…Aug 31, 2014 · N729AR · Substantial damageNTSB CEN14LA336Accident · 1 fatalLimited investigationFinal reportNorthwing Design Apache Sport · Omro, WIThe pilot’s failure to maintain adequate airspeed to compensate for a newly installed wing, which resulted in the aircraft stalling and the subsequent loss of control.Jul 3, 2014 · N2725T · Substantial damageNTSB CEN14CA155Accident · No injuriesData collectionFinal reportCessna 172M · South Bend, INThe pilot involved and the phase of flight when the damage was incurred were not determined.Feb 26, 2014 · N9801V · Substantial damageNTSB ENG14IA016Incident · No injuriesIncident investigationFinal reportDouglas C 118A · Emmonak, AKThe fatigue failure of the lower wing skin due to aging mechanisms. Contributing to the failure were lower wing skin inspection procedures insufficient to identify the crack.Feb 25, 2014 · N351CE · Minor damageNTSB CEN14LA148Accident · 1 fatalLimited investigationFinal reportVans RV-9A · Tribune, KSThe pilot’s failure to maintain airplane control while conducting high-g turns, which resulted in impact with terrain. Contributing to the accident was the pilot’s improper decision to initiate the flight with a severe progressive neurological disorder that…Feb 23, 2014 · N7872 · Substantial damageNTSB ERA13FA432Accident · 1 fatalField investigationFinal reportLowerre Bruce T S 12-E · Pahokee, FLUndetermined because the wreckage was not recovered.Sep 27, 2013 · N220BL · Substantial damageNTSB ANC13FAMS2Accident · 1 fatalMissing aircraftFinal reportPiper PA-32-260 · Yakutat, AKUndetermined because the pilot and airplane were not found.Sep 9, 2013 · N3705W · DestroyedNTSB ERA13FA349Accident · 2 fatalField investigationFinal reportBeech A36 · Alma, GADescent from cruise flight until impact with trees and the ground for reasons that could not be determined because postaccident examination of the wreckage did not reveal any anomalies that would have precluded normal operation and the pilot's autopsy and…Aug 4, 2013 · N1831W · DestroyedNTSB WPR13CA348Accident · No injuriesData collectionFinal reportCessna 172S · Phoenix, AZA substantially damaged firewall that occurred during an unknown phase of flight for reasons that could not be determined.Jul 26, 2013 · N635ND · Substantial damageNTSB WPR13LA203Accident · 1 fatalLimited investigationFinal reportHoffman RV-4 · Bullhead City, AZThe pilot’s suicidal act.Apr 20, 2013 · N194BD · Substantial damageNTSB ANC13FAMS1Accident · 1 fatalMissing aircraftFinal reportPiper PA-18-150 · Kenai, AKUndetermined. The airplane and pilot were not found.Oct 13, 2012 · N444LZ · DestroyedNTSB ERA12LA537Accident · 2 fatalLimited investigationFinal reportQuad City Ultralights Challenger II · Ocala, FLThe pilot's inadvertent overstress of the airplane, which resulted in the in-flight separation of the outer section of the right wing.Aug 30, 2012 · N2481P · Substantial damageNTSB WPR12FAMS1Accident · 1 fatalMissing aircraftFinal reportJeffko Glasair · Tonasket, WAThe cause of the accident is undetermined as the airplane has not been located and remains missing.Jul 23, 2012 · N743CA · Substantial damageNTSB ERA12CA466Accident · No injuriesData collectionFinal reportCessna 172P · St. Augustine, FLUndetermined due to a lack of information about when the damage to the airplane occurred.Jul 18, 2012 · N127EP · Substantial damageNTSB ERA12FAMS2Accident · 1 fatalMissing aircraftFinal reportCessna 150F · Atlantic Ocean (Atlantic Ocean)Undetermined (airplane and pilot missing).Jun 8, 2012 · N8281S · Substantial damageNTSB CEN12FAMS1Accident · 1 fatalMissing aircraftFinal reportPiper PA-31-325 · Duluth, MNUndetermined because the airplane was not found.Jun 8, 2012 · N174BH · Substantial damageNTSB CEN12LA352Accident · No injuriesLimited investigationFinal reportGrumman G164 · Higginson, ARThe partial loss of engine power for reasons that could not be determined due to impact and fire damage.Jun 7, 2012 · N779EF · Substantial damage