Below is the embedded Google Doc containing details regarding SQL queries and analysis.
Head to this link for a Google Drive folder of all resulting data from the queries run in SQL
This project uses fully synthetic patient-level fall data. Hospital names are used only as fictional labels for the exercise; the results do not describe actual patients, hospitals, or health-system performance.
I repeated the cleaning and analysis in Google BigQuery so I could practice a SQL-based workflow independently from the spreadsheet version.
The SQL workflow created a reusable cleaned event table, joined the fall-reason lookup table, derived new analysis fields, and then used focused queries to explore:
Fall timing
Mobility change
Injury severity
Unit-specific fall reasons
Provider presence
Cognitive status
Repeat fallers
Morse risk groups
Data anomalies
Correlations between numeric variables
The primary query standardized hospital names and timestamps, cleaned categorical fields, normalized fall-reason codes, calculated TUG change, created an ordinal injury-severity score, and joined the fall-reason lookup table.
CREATE OR REPLACE TABLE `hospital-falls.IL_hospital_falls.clean_fall_incidents` AS
WITH base_cleaned AS (
SELECT
Incident_ID,
Patient_MRN,
Patient_Age,
Unit_Type,
Morse_Fall_Score,
CASE
WHEN REGEXP_CONTAINS(UPPER(TRIM(Hospital_Name)), r'NORTHWESTERN|NM|NMH')
THEN 'Northwestern Memorial Hospital'
WHEN REGEXP_CONTAINS(UPPER(TRIM(Hospital_Name)), r'RUSH')
THEN 'Rush University Medical Center'
WHEN REGEXP_CONTAINS(UPPER(TRIM(Hospital_Name)), r'UCHICAGO')
THEN 'UChicago Medicine'
WHEN REGEXP_CONTAINS(UPPER(TRIM(Hospital_Name)), r'ADVOCATE')
THEN 'Advocate Illinois Masonic Medical Center'
ELSE INITCAP(TRIM(Hospital_Name))
END AS Cleaned_Hospital_Name,
CASE
WHEN SAFE_CAST(Fall_DateTime AS DATETIME) IS NOT NULL
THEN SAFE_CAST(Fall_DateTime AS DATETIME)
ELSE PARSE_DATETIME('%m/%d/%Y %H:%M', TRIM(Fall_DateTime))
END AS Fall_DateTime,
COALESCE(
NULLIF(INITCAP(TRIM(Cognitive_Status)), ''),
'Not Documented'
) AS Cleaned_Cognitive_Status,
COALESCE(
NULLIF(INITCAP(TRIM(Injury_Severity)), ''),
'None'
) AS Cleaned_Injury_Severity,
CASE
WHEN Provider_Present IS TRUE
OR UPPER(TRIM(CAST(Provider_Present AS STRING))) IN ('Y','YES','TRUE')
THEN 'Yes'
WHEN Provider_Present IS FALSE
OR UPPER(TRIM(CAST(Provider_Present AS STRING))) IN ('N','NO','FALSE')
THEN 'No'
ELSE 'Not Documented'
END AS Cleaned_Provider_Present,
CASE
WHEN REGEXP_CONTAINS(UPPER(TRIM(Fall_Reason_Code)), r'TOIL|BATH')
THEN 'TOIL'
WHEN REGEXP_CONTAINS(UPPER(TRIM(Fall_Reason_Code)), r'AMB|WALK')
THEN 'AMB'
WHEN REGEXP_CONTAINS(UPPER(TRIM(Fall_Reason_Code)), r'TRANS')
THEN 'TRANS'
WHEN REGEXP_CONTAINS(UPPER(TRIM(Fall_Reason_Code)), r'MED')
THEN 'MED'
ELSE 'OTHER'
END AS Cleaned_Reason_Code,
Post_Fall_TUG - Pre_Fall_TUG AS TUG_Change_Seconds,
ROUND(
(Post_Fall_TUG - Pre_Fall_TUG) / NULLIF(Pre_Fall_TUG,0),
4
) AS TUG_Percent_Decline,
CASE
WHEN COALESCE(NULLIF(INITCAP(TRIM(Injury_Severity)), ''), 'None') = 'None'
THEN 0
WHEN NULLIF(INITCAP(TRIM(Injury_Severity)), '') = 'Minor'
THEN 1
WHEN NULLIF(INITCAP(TRIM(Injury_Severity)), '') = 'Major'
THEN 2
WHEN NULLIF(INITCAP(TRIM(Injury_Severity)), '') = 'Sentinel Event'
THEN 3
ELSE 0
END AS Injury_Severity_Score,
Pre_Fall_TUG,
Post_Fall_TUG
FROM `hospital-falls.IL_hospital_falls.fall_incidents_log`
)
SELECT
b.Incident_ID,
b.Cleaned_Hospital_Name,
b.Patient_MRN,
b.Patient_Age,
b.Unit_Type,
b.Fall_DateTime,
FORMAT_DATETIME('%A', b.Fall_DateTime) AS Day_of_Week,
EXTRACT(HOUR FROM b.Fall_DateTime) AS Hour_of_Day,
b.Morse_Fall_Score,
b.Cleaned_Cognitive_Status,
b.Pre_Fall_TUG,
b.Post_Fall_TUG,
b.TUG_Change_Seconds,
b.TUG_Percent_Decline,
b.Cleaned_Provider_Present,
b.Cleaned_Reason_Code,
COALESCE(l.string_field_1, 'Other / Unclassified Cause') AS Reason_Description,
COALESCE(l.string_field_2, 'Tier 3') AS Preventability_Tier,
b.Cleaned_Injury_Severity,
b.Injury_Severity_Score
FROM base_cleaned b
LEFT JOIN `hospital-falls.IL_hospital_falls.lookup_fall_reasons` l
ON b.Cleaned_Reason_Code = l.string_field_0
AND l.string_field_0 != 'Reason_Code';
Patient age
The SQL master table retained Patient_Age as supplied.
It did not replace the two missing ages or the implausible age of 210.
A later anomaly query flagged the 210 value, and the correlation query excluded ages over 120.
This differs from the Google Sheets workflow, which replaced the two missing ages and age 210 with the median of 78.
Injury severity
The original SQL cleaning query converted blank injury-severity values to None.
The Sheets workflow labeled those same missing values Not Documented.
Because a missing value does not prove that no injury occurred, I treat these 36 records as undocumented when interpreting results in the final portfolio.
Provider presence
The SQL workflow standardizes provider presence into:
Yes
No
Not Documented
I do not use "No" as a formal witnessed/unwitnessed fall measure. It means only that a provider was not documented as present in the room.
Fall reason
Unmatched reason codes are routed to OTHER and then assigned:
Other / Unclassified Cause
Tier 3
The preventability tier is synthetic and not a validated clinical standard.
Purpose: Identify the hour/day combinations with the largest number of incidents.
The leading combination was:
Friday at 00:00
3 falls
Average patient age: 91.0
Average Morse score: 111.7
Saturday at 03:00 also had 3 incidents.
Several other hour/day combinations contained 2 incidents.
These small differences do not support strong conclusions about staffing or shift-level risk.
Average TUG changes increased with injury severity.
Major injury
20 incidents
Average TUG increase: 12.72 seconds
Average relative increase: 55.40%
Minor injury
26 incidents
Average increase: 6.43 seconds
Average relative increase: 37.57%
SQL "None" category
36 incidents
Average increase: 2.63 seconds
Average relative increase: 22.97%
These records include missing injury-severity values and should not be interpreted as confirmed no-injury events.
Sentinel Event
3 incidents
Average increase: 18.57 seconds
Average relative increase: 63.94%
The most common reason differed by care setting:
Med-Surg: Unassisted Toileting / Urgency — 16 incidents
Geriatric Psych: Ambulation Loss of Balance — 9 incidents
Acute Rehab: Bed/Chair Transfer Slippage — 7 incidents
ICU: Medication-Induced Orthostatic Hypotension — 7 incidents
Total incident counts:
Northwestern Memorial Hospital: 28
Rush University Medical Center: 22
UChicago Medicine: 18
Advocate Illinois Masonic Medical Center: 17
Major injuries:
Northwestern: 6
Rush: 4
UChicago: 6
Advocate: 4
Sentinel events:
Northwestern: 1
Rush: 1
UChicago: 0
Advocate: 1
These are counts rather than rates because no hospital-volume denominator exists.
Unassisted Toileting / Urgency
26 total
25 provider not present
1 not documented
Ambulation Loss of Balance
25 total
24 provider not present
1 not documented
Bed/Chair Transfer Slippage
22 total
10 provider not present
6 provider present
6 not documented
Medication-Induced Orthostatic Hypotension
10 total
4 provider not present
6 provider present
Other / Unclassified Cause
2 total
1 provider not present
1 not documented
Geriatric Psych
Average increase: 14.62 seconds
Average relative increase: 56.10%
Med-Surg
5.65 seconds
35.50%
ICU
3.91 seconds
29.08%
Acute Rehab
2.41 seconds
21.58%
Provider not present: average pre-fall TUG 18.04 seconds across 64 incidents
Provider present: 11.06 seconds across 12 incidents
Not documented: 10.07 seconds across 9 incidents
Sunday: 14
Monday: 13
Wednesday: 13
Friday: 13
Thursday: 12
Tuesday: 10
Saturday: 10
Alert & Oriented
30 incidents
Average Morse score: 40.0
Mild Confusion
22 incidents
Average Morse score: 63.0
Severe Dementia
17 incidents
Average Morse score: 94.4
Sun-downing
11 incidents
Average Morse score: 103.6
Not Documented
5 incidents
Average Morse score: 65.0
The original version of this query contained a typo: Geriatric Pysch instead of Geriatric Psych.
That error caused all Geriatric Psych counts in the query output to incorrectly appear as zero.
The corrected results are:
Unassisted Toileting / Urgency
Acute Rehab: 0
Geriatric Psych: 9
ICU: 1
Med-Surg: 16
Total: 26
Ambulation Loss of Balance
Acute Rehab: 6
Geriatric Psych: 9
ICU: 0
Med-Surg: 10
Total: 25
Bed/Chair Transfer Slippage
Acute Rehab: 7
Geriatric Psych: 0
ICU: 6
Med-Surg: 9
Total: 22
Medication-Induced Orthostatic Hypotension
Acute Rehab: 0
Geriatric Psych: 0
ICU: 7
Med-Surg: 3
Total: 10
Other / Unclassified Cause
Acute Rehab: 1
Geriatric Psych: 0
ICU: 0
Med-Surg: 1
Total: 2
Corrected SQL:
SELECT
Reason_Description AS Fall_Reason,
SUM(CASE
WHEN Unit_Type = 'Acute Rehab' THEN 1
ELSE 0
END) AS Acute_Rehab_Falls,
SUM(CASE
WHEN Unit_Type = 'Geriatric Psych' THEN 1
ELSE 0
END) AS Geriatric_Psych_Falls,
SUM(CASE
WHEN Unit_Type = 'ICU' THEN 1
ELSE 0
END) AS ICU_Falls,
SUM(CASE
WHEN Unit_Type = 'Med-Surg' THEN 1
ELSE 0
END) AS Med_Surg_Falls,
COUNT(Incident_ID) AS Total_Fall_Count
FROM `hospital-falls.IL_hospital_falls.clean_fall_incidents`
GROUP BY Fall_Reason
ORDER BY Total_Fall_Count DESC;
Major
20 incidents
All 20 provider not present
Sentinel Event
3 incidents
All 3 provider not present
Minor
26 incidents
23 provider not present
3 provider present
SQL "None"
36 incidents
18 provider not present
9 provider present
9 not documented
MRN-100184: 3 incidents
MRN-101961: 2 incidents
MRN-100391: 2 incidents
MRN-101239: 2 incidents
Together, the four repeat fallers accounted for 9 incidents.
Medium Risk: Morse 25–45
24 incidents
Average age: 73.1
Average baseline TUG: 9.95 seconds
Average TUG increase: 2.04 seconds
Average relative increase: 20.24%
Average injury-severity score: 0.00
High Risk: Morse 46+
60 incidents
Average age: 81.6
Average baseline TUG: 18.79 seconds
Average TUG increase: 8.67 seconds
Average relative increase: 43.22%
Average injury-severity score: 1.25
One incident had no documented Morse score.
Alert & Oriented
30 incidents
Primary reason: Bed/Chair Transfer Slippage
Average TUG increase: 22.7%
Major or sentinel outcomes: 0
Mild Confusion
22 incidents
Primary reason: Unassisted Toileting / Urgency
Average TUG increase: 35.3%
Major or sentinel outcomes: 2
Severe Dementia
17 incidents
Primary reason: Ambulation Loss of Balance
Average TUG increase: 53.8%
Major or sentinel outcomes: 15
Sun-downing
11 incidents
Primary reason: Ambulation Loss of Balance
Average TUG increase: 52.3%
Major or sentinel outcomes: 6
Not Documented
5 incidents
Primary reason: Ambulation Loss of Balance
Average TUG increase: 31.0%
Major or sentinel outcomes: 0
One clear anomaly was identified:
Incident ID: FALL-2026-043
Hospital: UChicago Medicine
Patient age: 210
Morse score: 40
TUG increase: 2.8 seconds
The query returned:
No Morse scores greater than 125
No negative TUG changes
After excluding ages above 120 and missing Morse scores:
Age vs. injury severity: 0.770
Age vs. mobility decline: 0.786
Morse score vs. injury severity: 0.872
Morse score vs. mobility decline: 0.887
Mobility decline vs. injury severity: 0.869
Absolute TUG increase vs. injury severity: 0.879
These correlations are unusually strong and should be understood as properties of a small synthetic dataset, not as generalizable clinical effect sizes.
The strongest analytical pattern was the difference in fall mechanisms across unit types rather than a simple hospital ranking. This supports the idea of targeted prevention strategies instead of a one-size-fits-all approach.
All major and sentinel outcomes occurred when a provider was documented as not present. Because the dataset is synthetic and observational, I describe this as an association rather than evidence that staff presence prevented injury.
Morse score, cognitive impairment, injury severity, and post-fall TUG change were strongly aligned within the synthetic data. These relationships demonstrate the analytical workflow but should not be interpreted as real-world estimates.
The validated Morse grouping also corrected an earlier project statement that all patients were high risk:
60 High Risk
24 Medium Risk
1 missing score
Timing patterns were relatively diffuse and do not support a strong staffing-by-time conclusion.
If I continued refining the SQL pipeline, I would:
Create an explicit Cleaned_Patient_Age field in the master table so the age-cleaning rule is consistent and auditable.
Change blank injury severity from None to Not Documented unless the source explicitly confirms that no injury occurred.
Replace the broad NM hospital regex with more specific matching if the logic were ever applied to a larger dataset.
Add row-count and category-reconciliation checks immediately after creation of the master cleaned table.
Add exposure denominators such as patient-days or occupied bed-days before comparing fall performance across units or hospitals in a real operational dataset.