The SQL quality-assurance process was completed before calculating final CAUTI or CLABSI candidate rates.
The workflow moved from basic structural checks to increasingly specific clinical-data logic:
Source counts and primary keys
→ Missing values and foreign keys
→ Categorical and date validation
→ Unit standardization
→ Device-date reconciliation
→ Device-day calculation
→ Infection timing and device association
→ Surveillance-review classification
→ Bundle-audit validation
→ Final monthly process/outcome validation
The goal was not to make every imperfect source field disappear. Original values were preserved, and new analytical fields were created where a defensible cleaning rule was necessary.
SELECT COUNT(*) AS patient_rows
FROM `hospital_infections.patients_raw`;
SELECT COUNT(*) AS encounter_rows
FROM `hospital_infections.encounters_raw`;
SELECT COUNT(*) AS device_rows
FROM `hospital_infections.device_events_raw`;
SELECT COUNT(*) AS infection_rows
FROM `hospital_infections.infection_surveillance_raw`;
Results:
patients: 1,000
encounters: 1,194
device_events: 865
infection_surveillance: 98
All source counts matched the expected generated dataset.
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT patient_id) AS unique_patient_ids,
COUNT(*) - COUNT(DISTINCT patient_id) AS duplicate_patient_ids
FROM `hospital_infections.patients_raw`;
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT encounter_id) AS unique_encounters,
COUNT(*) - COUNT(DISTINCT encounter_id) AS duplicate_encounters
FROM `hospital_infections.encounters_raw`;
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT device_id) AS unique_devices,
COUNT(*) - COUNT(DISTINCT device_id) AS duplicate_device_ids
FROM `hospital_infections.device_events_raw`;
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT infection_id) AS unique_infection_ids,
COUNT(*) - COUNT(DISTINCT infection_id) AS duplicate_infection_ids
FROM `hospital_infections.infection_surveillance_raw`;
Result: 0 duplicate primary keys across all four original source tables.
SELECT
COUNT(*) AS total_encounters,
SUM(CASE WHEN patient_id IS NULL THEN 1 ELSE 0 END) AS missing_patient_id,
SUM(CASE WHEN admission_date IS NULL THEN 1 ELSE 0 END) AS missing_admission_date,
SUM(CASE WHEN discharge_date IS NULL THEN 1 ELSE 0 END) AS missing_discharge_date,
SUM(CASE WHEN unit IS NULL THEN 1 ELSE 0 END) AS missing_unit,
SUM(CASE WHEN primary_diagnosis IS NULL THEN 1 ELSE 0 END) AS missing_primary_diagnosis
FROM `hospital_infections.encounters_raw`;
Result: 14 missing discharge dates and no missing values in the other fields checked.
SELECT
COUNT(*) AS total_device_events,
SUM(CASE WHEN removal_date IS NULL THEN 1 ELSE 0 END) AS missing_removal_date,
SUM(CASE WHEN insertion_date IS NULL THEN 1 ELSE 0 END) AS missing_insertion_date,
SUM(CASE WHEN insertion_unit IS NULL THEN 1 ELSE 0 END) AS missing_insertion_unit
FROM `hospital_infections.device_events_raw`;
Result: 7 missing removal dates.
SELECT
COUNT(*) AS total_surveillance_records,
SUM(CASE WHEN device_id IS NULL THEN 1 ELSE 0 END) AS missing_device_id,
SUM(CASE WHEN culture_date IS NULL THEN 1 ELSE 0 END) AS missing_culture_date,
SUM(CASE WHEN organism IS NULL THEN 1 ELSE 0 END) AS missing_organism,
SUM(CASE WHEN nhsn_criteria_met IS NULL THEN 1 ELSE 0 END) AS missing_nhsn_flag
FROM `hospital_infections.infection_surveillance_raw`;
Result:
35 missing device IDs
4 missing culture dates
0 missing organisms
0 missing source surveillance flags
SELECT COUNT(*) AS orphaned_encounters
FROM `hospital_infections.encounters_raw` AS e
LEFT JOIN `hospital_infections.patients_raw` AS p
ON e.patient_id = p.patient_id
WHERE p.patient_id IS NULL;
SELECT COUNT(*) AS orphaned_device_records
FROM `hospital_infections.device_events_raw` AS d
LEFT JOIN `hospital_infections.encounters_raw` AS e
ON d.encounter_id = e.encounter_id
WHERE e.encounter_id IS NULL;
SELECT COUNT(*) AS orphaned_surveillance_encounters
FROM `hospital_infections.infection_surveillance_raw` AS i
LEFT JOIN `hospital_infections.encounters_raw` AS e
ON i.encounter_id = e.encounter_id
WHERE e.encounter_id IS NULL;
SELECT COUNT(*) AS orphaned_surveillance_devices
FROM `hospital_infections.infection_surveillance_raw` AS i
LEFT JOIN `hospital_infections.device_events_raw` AS d
ON i.device_id = d.device_id
WHERE i.device_id IS NOT NULL
AND d.device_id IS NULL;
Result: 0 orphaned foreign keys.
The 35 surveillance rows with a NULL device ID were retained because a missing link is different from an invalid foreign key.
SELECT
unit,
COUNT(*) AS encounter_count
FROM `hospital_infections.encounters_raw`
GROUP BY unit
ORDER BY encounter_count DESC;
Fourteen raw labels represented three analytical units.
SELECT
insertion_unit,
COUNT(*) AS device_count
FROM `hospital_infections.device_events_raw`
GROUP BY insertion_unit
ORDER BY device_count DESC;
The device table contained the same 14 unit-name variants.
SELECT
device_type,
COUNT(*) AS device_count
FROM `hospital_infections.device_events_raw`
GROUP BY device_type;
Result:
535 Foley episodes
330 central-line episodes
SELECT
nhsn_criteria_met,
COUNT(*) AS surveillance_records
FROM `hospital_infections.infection_surveillance_raw`
GROUP BY nhsn_criteria_met;
Result:
TRUE: 67
FALSE: 31
These values describe the synthetic source field. They were not accepted as the final analytical classification without additional review.
SELECT COUNT(*) AS invalid_encounter_dates
FROM `hospital_infections.encounters_raw`
WHERE discharge_date IS NOT NULL
AND discharge_date < admission_date;
Result: 0.
SELECT COUNT(*) AS insertion_before_admission
FROM `hospital_infections.device_events_raw` AS d
JOIN `hospital_infections.encounters_raw` AS e
ON d.encounter_id = e.encounter_id
WHERE d.insertion_date < e.admission_date;
Result: 0.
SELECT COUNT(*) AS insertion_after_discharge
FROM `hospital_infections.device_events_raw` AS d
JOIN `hospital_infections.encounters_raw` AS e
ON d.encounter_id = e.encounter_id
WHERE e.discharge_date IS NOT NULL
AND d.insertion_date > e.discharge_date;
Result: 0.
SELECT COUNT(*) AS removal_before_insertion
FROM `hospital_infections.device_events_raw`
WHERE removal_date IS NOT NULL
AND removal_date < insertion_date;
Result: 0.
SELECT COUNT(*) AS removal_after_discharge
FROM `hospital_infections.device_events_raw` AS d
JOIN `hospital_infections.encounters_raw` AS e
ON d.encounter_id = e.encounter_id
WHERE d.removal_date IS NOT NULL
AND e.discharge_date IS NOT NULL
AND d.removal_date > e.discharge_date;
Result: 54 records.
Inspection query:
SELECT
d.device_id,
d.encounter_id,
d.device_type,
d.insertion_date,
d.insertion_unit,
d.removal_date,
e.admission_date,
e.discharge_date
FROM `hospital_infections.device_events_raw` AS d
JOIN `hospital_infections.encounters_raw` AS e
ON d.encounter_id = e.encounter_id
WHERE d.removal_date IS NOT NULL
AND e.discharge_date IS NOT NULL
AND d.removal_date > e.discharge_date
ORDER BY e.discharge_date;
SELECT COUNT(*) AS culture_after_discharge
FROM `hospital_infections.infection_surveillance_raw` AS i
JOIN `hospital_infections.encounters_raw` AS e
ON i.encounter_id = e.encounter_id
WHERE i.culture_date IS NOT NULL
AND e.discharge_date IS NOT NULL
AND i.culture_date > e.discharge_date;
Result: 7 records.
Inspection query:
SELECT
i.infection_id,
i.encounter_id,
i.device_id,
i.culture_date,
e.discharge_date,
e.admission_date,
i.organism,
i.nhsn_criteria_met
FROM `hospital_infections.infection_surveillance_raw` AS i
JOIN `hospital_infections.encounters_raw` AS e
ON i.encounter_id = e.encounter_id
WHERE i.culture_date IS NOT NULL
AND e.discharge_date IS NOT NULL
AND i.culture_date > e.discharge_date
ORDER BY i.culture_date;
Post-discharge cultures were flagged for later surveillance review rather than automatically included or excluded.
A mapping table was created so source values remained traceable rather than being overwritten.
CREATE OR REPLACE TABLE `hospital_infections.unit_mapping` AS
SELECT 'ICU' AS raw_unit, 'ICU' AS standardized_unit
UNION ALL SELECT 'icu', 'ICU'
UNION ALL SELECT 'Intensive Care Unit', 'ICU'
UNION ALL SELECT 'MICU', 'ICU'
UNION ALL SELECT 'SICU', 'ICU'
UNION ALL SELECT 'Med-Surg', 'Med-Surg'
UNION ALL SELECT 'Med/Surg', 'Med-Surg'
UNION ALL SELECT 'MedSurg', 'Med-Surg'
UNION ALL SELECT 'med surg', 'Med-Surg'
UNION ALL SELECT 'Medical-Surgical', 'Med-Surg'
UNION ALL SELECT 'Telemetry', 'Telemetry'
UNION ALL SELECT 'Tele', 'Telemetry'
UNION ALL SELECT 'TELE', 'Telemetry'
UNION ALL SELECT 'Cardiac Tele', 'Telemetry';
Validation:
SELECT
COUNT(*) AS mapping_rows,
COUNT(DISTINCT raw_unit) AS unique_raw_units,
COUNT(DISTINCT standardized_unit) AS standardized_units
FROM `hospital_infections.unit_mapping`;
Result:
14 mapping rows
14 unique raw labels
3 standardized units
Unmapped encounter check:
SELECT
e.unit,
COUNT(*) AS unmapped_records
FROM `hospital_infections.encounters_raw` AS e
LEFT JOIN `hospital_infections.unit_mapping` AS m
ON e.unit = m.raw_unit
WHERE m.raw_unit IS NULL
GROUP BY e.unit;
Result: 0.
Unmapped device-unit check:
SELECT
d.insertion_unit,
COUNT(*) AS unmapped_records
FROM `hospital_infections.device_events_raw` AS d
LEFT JOIN `hospital_infections.unit_mapping` AS m
ON d.insertion_unit = m.raw_unit
WHERE m.raw_unit IS NULL
GROUP BY d.insertion_unit;
Result: 0.
CREATE OR REPLACE TABLE `hospital_infections.encounters_clean` AS
SELECT
e.encounter_id,
e.patient_id,
e.admission_date,
e.discharge_date,
e.unit AS unit_raw,
m.standardized_unit AS unit_clean,
e.primary_diagnosis
FROM `hospital_infections.encounters_raw` AS e
LEFT JOIN `hospital_infections.unit_mapping` AS m
ON e.unit = m.raw_unit;
Validation:
SELECT
unit_clean,
COUNT(*) AS encounter_count
FROM `hospital_infections.encounters_clean`
GROUP BY unit_clean
ORDER BY encounter_count DESC;
Results:
Med-Surg: 586 encounters
ICU: 369
Telemetry: 239
Total: 1,194
CREATE OR REPLACE TABLE `hospital_infections.device_events_clean` AS
SELECT
d.device_id,
d.encounter_id,
d.device_type,
d.insertion_date,
d.removal_date,
d.insertion_unit AS insertion_unit_raw,
m.standardized_unit AS insertion_unit_clean
FROM `hospital_infections.device_events_raw` AS d
LEFT JOIN `hospital_infections.unit_mapping` AS m
ON d.insertion_unit = m.raw_unit;
Validation confirmed all 865 rows remained.
Device counts by standardized unit:
SELECT
insertion_unit_clean,
COUNT(*) AS device_count
FROM `hospital_infections.device_events_clean`
GROUP BY insertion_unit_clean
ORDER BY device_count DESC;
Results:
ICU: 380
Med-Surg: 337
Telemetry: 148
Diagnostic classification:
SELECT
CASE
WHEN d.removal_date IS NULL
THEN 'Missing Removal Date'
WHEN e.discharge_date IS NOT NULL
AND d.removal_date > e.discharge_date
THEN 'Removal After Discharge'
ELSE 'No Issue'
END AS device_date_issue_type,
COUNT(*) AS device_count
FROM `hospital_infections.device_events_clean` AS d
LEFT JOIN `hospital_infections.encounters_clean` AS e
ON d.encounter_id = e.encounter_id
GROUP BY device_date_issue_type
ORDER BY device_count DESC;
Results:
No Issue: 804
Removal After Discharge: 54
Missing Removal Date: 7
The cleaned table preserved the original removal date and created a separate analytical end date.
CREATE OR REPLACE TABLE
`hospital_infections.device_events_clean_v2` AS
SELECT
d.device_id,
d.encounter_id,
d.device_type,
d.insertion_date,
d.removal_date AS removal_date_raw,
e.admission_date,
e.discharge_date,
d.insertion_unit_raw,
d.insertion_unit_clean,
CASE
WHEN d.removal_date IS NOT NULL
AND e.discharge_date IS NOT NULL
AND d.removal_date > e.discharge_date
THEN e.discharge_date
WHEN d.removal_date IS NULL
AND e.discharge_date IS NOT NULL
THEN e.discharge_date
WHEN d.removal_date IS NOT NULL
THEN d.removal_date
ELSE NULL
END AS device_end_date_clean,
CASE
WHEN d.removal_date IS NULL
AND e.discharge_date IS NOT NULL
THEN 1
WHEN d.removal_date IS NOT NULL
AND e.discharge_date IS NOT NULL
AND d.removal_date > e.discharge_date
THEN 1
ELSE 0
END AS device_date_corrected_flag,
CASE
WHEN d.removal_date IS NULL
AND e.discharge_date IS NOT NULL
THEN 'Missing Removal Date - Capped at Discharge'
WHEN d.removal_date IS NOT NULL
AND e.discharge_date IS NOT NULL
AND d.removal_date > e.discharge_date
THEN 'Removal After Discharge - Capped at Discharge'
WHEN d.removal_date IS NULL
AND e.discharge_date IS NULL
THEN 'End Date Unable to Determine'
ELSE 'No Correction Needed'
END AS device_date_issue_type
FROM `hospital_infections.device_events_clean` AS d
LEFT JOIN `hospital_infections.encounters_clean` AS e
ON d.encounter_id = e.encounter_id;
Post-cleaning validation:
SELECT
COUNT(*) AS remaining_end_dates_after_discharge
FROM `hospital_infections.device_events_clean_v2`
WHERE discharge_date IS NOT NULL
AND device_end_date_clean > discharge_date;
Result: 0.
SELECT
COUNT(*) AS end_before_insertion
FROM `hospital_infections.device_events_clean_v2`
WHERE device_end_date_clean IS NOT NULL
AND device_end_date_clean < insertion_date;
Result: 0.
SELECT
COUNT(*) AS unresolved_device_end_dates
FROM `hospital_infections.device_events_clean_v2`
WHERE device_end_date_clean IS NULL;
Result: 0.
A total of 61 device episodes required analytical end-date reconciliation:
SELECT
COUNT(*) AS total_devices,
SUM(device_date_corrected_flag) AS corrected_devices,
ROUND(
100 * SAFE_DIVIDE(
SUM(device_date_corrected_flag),
COUNT(*)
),
1
) AS percent_corrected
FROM `hospital_infections.device_events_clean_v2`;
Result: 61 of 865 episodes, or 7.1%.
CREATE OR REPLACE TABLE
`hospital_infections.device_events_final` AS
SELECT
*,
CASE
WHEN device_end_date_clean IS NOT NULL
AND device_end_date_clean >= insertion_date
THEN DATE_DIFF(
device_end_date_clean,
insertion_date,
DAY
) + 1
ELSE NULL
END AS device_days
FROM `hospital_infections.device_events_clean_v2`;
Validation:
SELECT
COUNT(*) AS total_rows,
COUNTIF(device_days IS NULL) AS null_device_days,
COUNTIF(device_days <= 0) AS non_positive_device_days,
MIN(device_days) AS min_device_days,
MAX(device_days) AS max_device_days,
ROUND(AVG(device_days), 2) AS avg_device_days
FROM `hospital_infections.device_events_final`;
Final validated extract:
865 rows
0 NULL device-day values
0 nonpositive values
Minimum: 1 day
Maximum: 19 days
Average: approximately 4.52 days
Unit/device totals:
ICU — Central Line
Episodes: 164
Device-Days: 750
ICU — Foley
Episodes: 216
Device-Days: 1,010
Med-Surg — Central Line
Episodes: 119
Device-Days: 548
Med-Surg — Foley
Episodes: 218
Device-Days: 964
Telemetry — Central Line
Episodes: 47
Device-Days: 197
Telemetry — Foley
Episodes: 101
Device-Days: 443
An overlap check found no overlapping same-type device episodes within the same encounter.
SELECT
a.encounter_id,
a.device_type,
a.device_id AS device_1,
b.device_id AS device_2,
a.insertion_date AS device_1_start,
a.device_end_date_clean AS device_1_end,
b.insertion_date AS device_2_start,
b.device_end_date_clean AS device_2_end
FROM `hospital_infections.device_events_final` AS a
JOIN `hospital_infections.device_events_final` AS b
ON a.encounter_id = b.encounter_id
AND a.device_type = b.device_type
AND a.device_id < b.device_id
WHERE a.insertion_date <= b.device_end_date_clean
AND b.insertion_date <= a.device_end_date_clean;
Result: 0 overlapping records.
Initial admission-to-culture inspection:
SELECT
i.infection_id,
i.encounter_id,
i.device_id,
e.admission_date,
e.discharge_date,
i.culture_date,
i.organism,
i.nhsn_criteria_met,
CASE
WHEN i.culture_date IS NOT NULL
THEN DATE_DIFF(
i.culture_date,
e.admission_date,
DAY
)
ELSE NULL
END AS days_from_admission_to_culture
FROM `hospital_infections.infection_surveillance_raw` AS i
LEFT JOIN `hospital_infections.encounters_clean` AS e
ON i.encounter_id = e.encounter_id
ORDER BY
days_from_admission_to_culture,
i.infection_id;
A staging table added the final timing-proxy fields while retaining the source flag.
CREATE OR REPLACE TABLE
`hospital_infections.infection_surveillance_stage1` AS
SELECT
i.infection_id,
i.encounter_id,
i.device_id,
i.culture_date,
i.organism,
i.nhsn_criteria_met_raw,
e.admission_date,
e.discharge_date,
e.unit_raw,
e.unit_clean,
CASE
WHEN i.culture_date IS NOT NULL
THEN DATE_DIFF(
i.culture_date,
e.admission_date,
DAY
)
ELSE NULL
END AS days_from_admission_to_culture,
CASE
WHEN i.culture_date IS NOT NULL
AND e.discharge_date IS NOT NULL
THEN DATE_DIFF(
i.culture_date,
e.discharge_date,
DAY
)
ELSE NULL
END AS days_from_discharge_to_culture,
CASE
WHEN i.culture_date IS NULL
THEN 'Unable to Determine'
WHEN i.culture_date < e.admission_date
THEN 'Pre-Admission Culture'
WHEN DATE_DIFF(
i.culture_date,
e.admission_date,
DAY
) < 2
THEN 'Early / POA Timing Proxy'
ELSE 'Hospital-Onset Timing Proxy'
END AS infection_timing_category,
CASE
WHEN i.culture_date IS NOT NULL
AND e.discharge_date IS NOT NULL
AND i.culture_date > e.discharge_date
THEN 1
ELSE 0
END AS post_discharge_culture_flag,
CASE
WHEN i.culture_date IS NULL
THEN 'Missing Culture Date'
WHEN e.discharge_date IS NULL
THEN 'Discharge Date Missing'
WHEN i.culture_date <= e.discharge_date
THEN 'Not Post-Discharge'
WHEN DATE_DIFF(
i.culture_date,
e.discharge_date,
DAY
) = 1
THEN '1 Day After Discharge'
ELSE 'More Than 1 Day After Discharge'
END AS post_discharge_review_category
FROM `hospital_infections.infection_surveillance_clean` AS i
LEFT JOIN `hospital_infections.encounters_clean` AS e
ON i.encounter_id = e.encounter_id;
Timing results:
Hospital-Onset Timing Proxy: 94
Unable to Determine: 4
Early / POA Timing Proxy: 0
Cultures before admission: 0
Post-discharge cultures: 7
This result demonstrated why timing alone could not define an analytical infection event. Ninety-four dated cultures occurred late enough to meet the timing screen, but only a subset ultimately had sufficient device and source evidence to become analytical candidates.
CREATE OR REPLACE TABLE
`hospital_infections.infection_surveillance_stage2` AS
SELECT
s.*,
d.device_type,
d.insertion_date,
d.removal_date_raw,
d.device_end_date_clean,
d.insertion_unit_raw,
d.insertion_unit_clean,
d.device_days AS total_device_days,
CASE
WHEN s.device_id IS NULL
THEN 'No Linked Device'
WHEN d.device_id IS NULL
THEN 'Device ID Not Found'
ELSE 'Linked Device Found'
END AS device_link_status,
CASE
WHEN s.culture_date IS NOT NULL
AND d.insertion_date IS NOT NULL
THEN DATE_DIFF(
s.culture_date,
d.insertion_date,
DAY
) + 1
ELSE NULL
END AS device_day_at_culture_proxy,
CASE
WHEN s.culture_date IS NULL
THEN 'Unable to Determine - Missing Culture Date'
WHEN s.device_id IS NULL
THEN 'No Linked Device'
WHEN d.device_id IS NULL
THEN 'Unable to Determine - Device Not Found'
WHEN s.culture_date < d.insertion_date
THEN 'Culture Before Device Insertion'
WHEN s.culture_date BETWEEN
d.insertion_date AND d.device_end_date_clean
THEN 'Culture While Device Present'
WHEN DATE_DIFF(
s.culture_date,
d.device_end_date_clean,
DAY
) = 1
THEN 'Culture 1 Day After Device End'
ELSE 'Culture More Than 1 Day After Device End'
END AS culture_device_relationship,
CASE
WHEN s.culture_date IS NULL
THEN 'Unable to Determine - Missing Culture Date'
WHEN s.device_id IS NULL
THEN 'Not Assessable - No Linked Device'
WHEN d.device_id IS NULL
THEN 'Unable to Determine - Device Not Found'
WHEN s.culture_date < d.insertion_date
THEN 'Not Eligible - Culture Before Device'
WHEN s.culture_date BETWEEN
d.insertion_date AND d.device_end_date_clean
AND DATE_DIFF(
s.culture_date,
d.insertion_date,
DAY
) + 1 >= 3
THEN 'Eligible by Available Data'
WHEN DATE_DIFF(
s.culture_date,
d.device_end_date_clean,
DAY
) = 1
AND DATE_DIFF(
d.device_end_date_clean,
d.insertion_date,
DAY
) + 1 >= 3
THEN 'Eligible by Available Data'
WHEN (
s.culture_date BETWEEN
d.insertion_date AND d.device_end_date_clean
OR DATE_DIFF(
s.culture_date,
d.device_end_date_clean,
DAY
) = 1
)
THEN 'Not Eligible - Device Present <3 Days'
ELSE 'Not Eligible - Outside Device Window'
END AS device_association_eligibility
FROM `hospital_infections.infection_surveillance_stage1` AS s
LEFT JOIN `hospital_infections.device_events_final` AS d
ON s.device_id = d.device_id;
Device-link validation:
SELECT
device_link_status,
COUNT(*) AS surveillance_records
FROM `hospital_infections.infection_surveillance_stage2`
GROUP BY device_link_status;
Results:
Linked Device Found: 63
No Linked Device: 35
Device ID Not Found: 0
Eligibility summary:
SELECT
device_association_eligibility,
COUNT(*) AS surveillance_records
FROM `hospital_infections.infection_surveillance_stage2`
GROUP BY device_association_eligibility
ORDER BY surveillance_records DESC;
Results:
Eligible by Available Data: 57
Not Assessable - No Linked Device: 33
Unable to Determine - Missing Culture Date: 4
Not Eligible - Device Present <3 Days: 3
Not Eligible - Outside Device Window: 1
SELECT
nhsn_criteria_met_raw,
device_association_eligibility,
COUNT(*) AS surveillance_records
FROM `hospital_infections.infection_surveillance_stage2`
GROUP BY
nhsn_criteria_met_raw,
device_association_eligibility
ORDER BY
nhsn_criteria_met_raw,
surveillance_records DESC;
This comparison showed that device timing and the source surveillance flag were not always aligned.
Most importantly, 26 records were device-eligible by the available date logic while the source NHSN flag was FALSE.
These were not relabeled as infections. They were assigned to a review category because the project does not contain enough clinical information to independently override the source surveillance determination.
CREATE OR REPLACE TABLE
`hospital_infections.infection_surveillance_review` AS
SELECT
s.*,
CASE
WHEN s.culture_date IS NULL
THEN 'Needs Review - Missing Culture Date'
WHEN s.device_association_eligibility =
'Not Assessable - No Linked Device'
THEN 'Not Device-Assessable'
WHEN s.device_association_eligibility IN (
'Not Eligible - Device Present <3 Days',
'Not Eligible - Outside Device Window',
'Not Eligible - Culture Before Device'
)
AND s.nhsn_criteria_met_raw = TRUE
THEN 'Needs Review - NHSN Y but Device Not Eligible'
WHEN s.device_association_eligibility =
'Eligible by Available Data'
AND s.nhsn_criteria_met_raw = FALSE
THEN 'Needs Review - Device Eligible but NHSN N'
WHEN s.device_association_eligibility =
'Eligible by Available Data'
AND s.nhsn_criteria_met_raw = TRUE
AND s.infection_timing_category =
'Hospital-Onset Timing Proxy'
THEN 'Consistent Candidate'
WHEN s.nhsn_criteria_met_raw = FALSE
AND s.device_association_eligibility !=
'Eligible by Available Data'
THEN 'Consistent Non-Candidate'
ELSE 'Needs Review - Other'
END AS surveillance_review_status
FROM `hospital_infections.infection_surveillance_stage2` AS s;
Review-status summary:
SELECT
surveillance_review_status,
COUNT(*) AS surveillance_records
FROM `hospital_infections.infection_surveillance_review`
GROUP BY surveillance_review_status
ORDER BY surveillance_records DESC;
Final review results:
31 Consistent Candidates
26 Needs Review - Device Eligible but NHSN N
33 Not Device-Assessable
4 Needs Review - Missing Culture Date
4 Consistent Non-Candidates
0 NHSN-Y / device-ineligible conflicts
The 30 records requiring additional review were exported to a separate review table for inspection.
SELECT
infection_id,
encounter_id,
device_id,
device_type,
culture_date,
admission_date,
discharge_date,
unit_clean,
device_day_at_culture_proxy,
culture_device_relationship,
device_association_eligibility,
nhsn_criteria_met_raw,
infection_timing_category,
post_discharge_review_category,
surveillance_review_status
FROM `hospital_infections.infection_surveillance_review`
WHERE surveillance_review_status LIKE 'Needs Review%'
ORDER BY
surveillance_review_status,
device_type,
infection_id;
CREATE OR REPLACE TABLE
`hospital_infections.infection_surveillance_final` AS
SELECT
r.*,
CASE
WHEN r.surveillance_review_status =
'Consistent Candidate'
AND r.device_type = 'Foley'
THEN 'Analytical CAUTI Candidate'
WHEN r.surveillance_review_status =
'Consistent Candidate'
AND r.device_type = 'Central Line'
THEN 'Analytical CLABSI Candidate'
WHEN r.surveillance_review_status LIKE
'Needs Review%'
THEN 'Needs Surveillance Review'
WHEN r.surveillance_review_status =
'Not Device-Assessable'
THEN 'Not Device-Assessable'
WHEN r.surveillance_review_status =
'Consistent Non-Candidate'
THEN 'Not Device-Associated Candidate'
ELSE 'Unable to Determine'
END AS analytical_case_classification
FROM `hospital_infections.infection_surveillance_review` AS r;
Validation:
SELECT
analytical_case_classification,
COUNT(*) AS surveillance_records
FROM `hospital_infections.infection_surveillance_final`
GROUP BY analytical_case_classification
ORDER BY surveillance_records DESC;
Results:
Not Device-Assessable: 33
Needs Surveillance Review: 30
Analytical CAUTI Candidate: 16
Analytical CLABSI Candidate: 15
Not Device-Associated Candidate: 4
Total: 98.
The fifth table was independently checked before any process compliance was calculated.
SELECT
COUNT(*) AS total_rows,
COUNT(DISTINCT audit_id) AS unique_audit_ids,
COUNT(*) - COUNT(DISTINCT audit_id) AS duplicate_audit_ids
FROM `hospital_infections.device_bundle_audits`;
Result: 1,368 rows, 1,368 unique IDs, 0 duplicates.
SELECT
COUNT(*) AS total_audits,
COUNTIF(device_id IS NULL) AS missing_device_id,
COUNTIF(encounter_id IS NULL) AS missing_encounter_id,
COUNTIF(audit_date IS NULL) AS missing_audit_date,
COUNTIF(unit IS NULL) AS missing_unit,
COUNTIF(device_type IS NULL) AS missing_device_type,
COUNTIF(hand_hygiene_compliant IS NULL) AS missing_hand_hygiene,
COUNTIF(daily_necessity_review_completed IS NULL) AS missing_necessity_review,
COUNTIF(overall_bundle_compliant IS NULL) AS missing_overall_compliance
FROM `hospital_infections.device_bundle_audits`;
Result: 0 missing values in the required audit fields.
SELECT COUNT(*) AS orphaned_device_audits
FROM `hospital_infections.device_bundle_audits` AS a
LEFT JOIN `hospital_infections.device_events_final` AS d
ON a.device_id = d.device_id
WHERE d.device_id IS NULL;
Result: 0.
SELECT COUNT(*) AS device_encounter_mismatches
FROM `hospital_infections.device_bundle_audits` AS a
JOIN `hospital_infections.device_events_final` AS d
ON a.device_id = d.device_id
WHERE a.encounter_id != d.encounter_id;
Result: 0.
SELECT COUNT(*) AS invalid_unit_values
FROM `hospital_infections.device_bundle_audits`
WHERE unit NOT IN ('ICU', 'Med-Surg', 'Telemetry');
Result: 0.
SELECT COUNT(*) AS invalid_device_types
FROM `hospital_infections.device_bundle_audits`
WHERE device_type NOT IN ('Foley', 'Central Line');
Result: 0.
SELECT COUNT(*) AS invalid_shared_values
FROM `hospital_infections.device_bundle_audits`
WHERE hand_hygiene_compliant NOT IN (TRUE, FALSE)
OR daily_necessity_review_completed NOT IN (TRUE, FALSE)
OR overall_bundle_compliant NOT IN (TRUE, FALSE);
Result: 0.
SELECT COUNT(*) AS invalid_element_values
FROM `hospital_infections.device_bundle_audits`
WHERE closed_system_maintained NOT IN ('Y', 'N', 'NA')
OR drainage_bag_below_bladder NOT IN ('Y', 'N', 'NA')
OR unobstructed_flow NOT IN ('Y', 'N', 'NA')
OR dressing_clean_dry_intact NOT IN ('Y', 'N', 'NA')
OR hub_disinfection_compliant NOT IN ('Y', 'N', 'NA')
OR chg_bathing_completed NOT IN ('Y', 'N', 'NA');
Result: 0.
SELECT COUNT(*) AS invalid_foley_applicability
FROM `hospital_infections.device_bundle_audits`
WHERE device_type = 'Foley'
AND (
closed_system_maintained NOT IN ('Y', 'N')
OR drainage_bag_below_bladder NOT IN ('Y', 'N')
OR unobstructed_flow NOT IN ('Y', 'N')
OR dressing_clean_dry_intact != 'NA'
OR hub_disinfection_compliant != 'NA'
OR chg_bathing_completed != 'NA'
);
Result: 0.
SELECT COUNT(*) AS invalid_central_line_applicability
FROM `hospital_infections.device_bundle_audits`
WHERE device_type = 'Central Line'
AND (
dressing_clean_dry_intact NOT IN ('Y', 'N')
OR hub_disinfection_compliant NOT IN ('Y', 'N')
OR chg_bathing_completed NOT IN ('Y', 'N')
OR closed_system_maintained != 'NA'
OR drainage_bag_below_bladder != 'NA'
OR unobstructed_flow != 'NA'
);
Result: 0.
SELECT COUNT(*) AS audits_outside_device_window
FROM `hospital_infections.device_bundle_audits` AS a
JOIN `hospital_infections.device_events_final` AS d
ON a.device_id = d.device_id
WHERE a.audit_date < d.insertion_date
OR a.audit_date > d.device_end_date_clean;
Result: 0.
Audit timing ranged from Device Day 1 through Device Day 14, with an average sampled audit occurring around Device Day 3.07.
SELECT COUNT(*) AS unit_mismatches
FROM `hospital_infections.device_bundle_audits` AS a
JOIN `hospital_infections.device_events_final` AS d
ON a.device_id = d.device_id
WHERE a.unit != d.insertion_unit_clean;
Result: 0.
SELECT COUNT(*) AS device_type_mismatches
FROM `hospital_infections.device_bundle_audits` AS a
JOIN `hospital_infections.device_events_final` AS d
ON a.device_id = d.device_id
WHERE a.device_type != d.device_type;
Result: 0.
Foley:
SELECT COUNT(*) AS incorrect_foley_bundle_flags
FROM `hospital_infections.device_bundle_audits`
WHERE device_type = 'Foley'
AND overall_bundle_compliant !=
CASE
WHEN hand_hygiene_compliant = TRUE
AND daily_necessity_review_completed = TRUE
AND closed_system_maintained = 'Y'
AND drainage_bag_below_bladder = 'Y'
AND unobstructed_flow = 'Y'
THEN TRUE
ELSE FALSE
END;
Central line:
SELECT COUNT(*) AS incorrect_central_line_bundle_flags
FROM `hospital_infections.device_bundle_audits`
WHERE device_type = 'Central Line'
AND overall_bundle_compliant !=
CASE
WHEN hand_hygiene_compliant = TRUE
AND daily_necessity_review_completed = TRUE
AND dressing_clean_dry_intact = 'Y'
AND hub_disinfection_compliant = 'Y'
AND chg_bathing_completed = 'Y'
THEN TRUE
ELSE FALSE
END;
Result: 0 mismatches for either device type.
CREATE OR REPLACE TABLE
`hospital_infections.device_bundle_audits_final` AS
SELECT
a.*,
DATE_TRUNC(a.audit_date, MONTH) AS audit_month,
CASE
WHEN a.device_type = 'Foley'
THEN 'CAUTI'
WHEN a.device_type = 'Central Line'
THEN 'CLABSI'
END AS infection_type,
CASE
WHEN a.overall_bundle_compliant = TRUE
THEN 1
ELSE 0
END AS bundle_compliant_flag,
DATE_DIFF(
a.audit_date,
d.insertion_date,
DAY
) + 1 AS device_day_at_audit
FROM `hospital_infections.device_bundle_audits` AS a
JOIN `hospital_infections.device_events_final` AS d
ON a.device_id = d.device_id;
Validation returned:
1,368 total rows
1,368 unique audit IDs
0 invalid compliance flags
0 invalid device-day values
Audit coverage:
SELECT
COUNT(DISTINCT d.device_id) AS total_devices,
COUNT(DISTINCT a.device_id) AS audited_devices,
COUNT(DISTINCT d.device_id)
- COUNT(DISTINCT a.device_id)
AS devices_without_audits,
ROUND(
SAFE_DIVIDE(
COUNT(DISTINCT a.device_id),
COUNT(DISTINCT d.device_id)
) * 100,
1
) AS percent_devices_audited
FROM `hospital_infections.device_events_final` AS d
LEFT JOIN `hospital_infections.device_bundle_audits_final` AS a
ON d.device_id = a.device_id;
Results:
Total devices: 865
Audited devices: 812
Devices without a sampled audit: 53
Percent receiving at least one audit: 93.9%
The final Tableau quality table was checked for duplicate month/unit/type keys and recalculated rate and compliance values.
Rate validation:
SELECT COUNT(*) AS rate_mismatches
FROM `hospital_infections.tableau_quality_fact`
WHERE (
device_days > 0
AND ABS(
rate_per_1000_device_days
-
ROUND(
SAFE_DIVIDE(
candidate_infections,
device_days
) * 1000,
2
)
) > 0.01
)
OR (
device_days = 0
AND rate_per_1000_device_days IS NOT NULL
);
Result: 0 mismatches.
Compliance validation:
SELECT COUNT(*) AS compliance_mismatches
FROM `hospital_infections.tableau_quality_fact`
WHERE total_audits IS NOT NULL
AND ABS(
bundle_compliance_pct
-
ROUND(
SAFE_DIVIDE(
compliant_audits,
total_audits
) * 100,
1
)
) > 0.05;
Result: 0 mismatches.
Range validation:
SELECT COUNT(*) AS invalid_compliance_values
FROM `hospital_infections.tableau_quality_fact`
WHERE bundle_compliance_pct < 0
OR bundle_compliance_pct > 100;
Result: 0.
One expected reporting combination contained no exposure and no sampled process audit:
December 2024 | Telemetry | CLABSI
Its candidate count remained 0 while the candidate rate and audit/compliance measures remained NULL.
This distinction was intentional. No audit data is not the same as 0% compliance.