After QA and cleaning were complete, the analytical SQL workflow moved from individual records to quality-improvement measures.
The analysis was designed around four questions:
What are the analytical CAUTI and CLABSI candidate rates after accounting for device exposure?
How do rates and maintenance-bundle compliance vary across units?
Do device duration and individual maintenance elements identify more specific improvement opportunities?
What additional patient, event, and surveillance context should be available for investigation in Tableau?
Candidate counts and device-day denominators were combined into a unit-level rate table.
CREATE OR REPLACE TABLE
`hospital_infections.infection_rate_summary` AS
WITH candidate_counts AS (
SELECT
unit_clean AS unit,
CASE
WHEN analytical_case_classification =
'Analytical CAUTI Candidate'
THEN 'CAUTI'
WHEN analytical_case_classification =
'Analytical CLABSI Candidate'
THEN 'CLABSI'
END AS infection_type,
COUNT(DISTINCT infection_id) AS candidate_infections
FROM `hospital_infections.infection_surveillance_final`
WHERE analytical_case_classification IN (
'Analytical CAUTI Candidate',
'Analytical CLABSI Candidate'
)
GROUP BY
unit,
infection_type
),
device_denominators AS (
SELECT
insertion_unit_clean AS unit,
CASE
WHEN device_type = 'Foley'
THEN 'CAUTI'
WHEN device_type = 'Central Line'
THEN 'CLABSI'
END AS infection_type,
SUM(device_days) AS device_days
FROM `hospital_infections.device_events_final`
GROUP BY
unit,
infection_type
)
SELECT
d.unit,
d.infection_type,
COALESCE(c.candidate_infections, 0) AS candidate_infections,
d.device_days,
ROUND(
SAFE_DIVIDE(
COALESCE(c.candidate_infections, 0),
d.device_days
) * 1000,
2
) AS rate_per_1000_device_days
FROM device_denominators AS d
LEFT JOIN candidate_counts AS c
ON d.unit = c.unit
AND d.infection_type = c.infection_type
ORDER BY
d.unit,
d.infection_type;
Results:
ICU — CAUTI Candidates: 8
Device-Days: 1,010
Rate per 1,000: 7.92
ICU — CLABSI Candidates: 11
Device-Days: 750
Rate per 1,000: 14.67
Med-Surg — CAUTI Candidates: 5
Device-Days: 964
Rate per 1,000: 5.19
Med-Surg — CLABSI Candidates: 3
Device-Days: 548
Rate per 1,000: 5.47
Telemetry — CAUTI Candidates: 3
Device-Days: 443
Rate per 1,000: 6.77
Telemetry — CLABSI Candidates: 1
Device-Days: 197
Rate per 1,000: 5.08
The most prominent result was the ICU CLABSI candidate rate of 14.67 per 1,000 central-line days.
SELECT
infection_type,
SUM(candidate_infections) AS candidate_infections,
SUM(device_days) AS device_days,
ROUND(
SAFE_DIVIDE(
SUM(candidate_infections),
SUM(device_days)
) * 1000,
2
) AS hospital_rate_per_1000_device_days
FROM `hospital_infections.infection_rate_summary`
GROUP BY infection_type
ORDER BY infection_type;
Results:
CAUTI: 16 / 2,417 = 6.62 per 1,000 Foley-days
CLABSI: 15 / 1,495 = 10.03 per 1,000 central-line days
Rates were calculated from aggregated numerators and denominators rather than averaging individual unit or monthly rates.
A 12-month × 3-unit × 2-infection-type scaffold ensured that months without candidate events were retained.
CREATE OR REPLACE TABLE
`hospital_infections.month_unit_infections` AS
WITH months AS (
SELECT month
FROM UNNEST(
GENERATE_DATE_ARRAY(
DATE '2024-01-01',
DATE '2024-12-01',
INTERVAL 1 MONTH
)
) AS month
),
units AS (
SELECT 'ICU' AS unit
UNION ALL SELECT 'Med-Surg'
UNION ALL SELECT 'Telemetry'
),
infection_types AS (
SELECT 'CAUTI' AS infection_type
UNION ALL SELECT 'CLABSI'
)
SELECT
month,
unit,
infection_type
FROM months
CROSS JOIN units
CROSS JOIN infection_types
ORDER BY
month,
unit,
infection_type;
Expected and actual result: 72 rows.
CREATE OR REPLACE TABLE
`hospital_infections.monthly_infection_candidates` AS
SELECT
DATE_TRUNC(culture_date, MONTH) AS month,
unit_clean AS unit,
CASE
WHEN analytical_case_classification =
'Analytical CAUTI Candidate'
THEN 'CAUTI'
WHEN analytical_case_classification =
'Analytical CLABSI Candidate'
THEN 'CLABSI'
END AS infection_type,
COUNT(DISTINCT infection_id) AS candidate_infections
FROM `hospital_infections.infection_surveillance_final`
WHERE analytical_case_classification IN (
'Analytical CAUTI Candidate',
'Analytical CLABSI Candidate'
)
GROUP BY
month,
unit,
infection_type;
Device episodes were first expanded to one row per day so exposure crossing month boundaries could be attributed correctly.
CREATE OR REPLACE TABLE
`hospital_infections.device_days_daily` AS
SELECT
d.device_id,
d.encounter_id,
d.device_type,
d.insertion_unit_clean AS unit,
device_date
FROM `hospital_infections.device_events_final` AS d,
UNNEST(
GENERATE_DATE_ARRAY(
d.insertion_date,
d.device_end_date_clean,
INTERVAL 1 DAY
)
) AS device_date;
Monthly aggregation:
CREATE OR REPLACE TABLE
`hospital_infections.monthly_device_days` AS
SELECT
DATE_TRUNC(device_date, MONTH) AS month,
unit,
CASE
WHEN device_type = 'Foley'
THEN 'CAUTI'
WHEN device_type = 'Central Line'
THEN 'CLABSI'
END AS infection_type,
COUNT(*) AS device_days
FROM `hospital_infections.device_days_daily`
GROUP BY
month,
unit,
infection_type;
The monthly denominator totals reconciled to the annual totals:
2,417 Foley-days
1,495 central-line days
CREATE OR REPLACE TABLE
`hospital_infections.tableau_infection_fact` AS
SELECT
m.month,
m.unit,
m.infection_type,
COALESCE(i.candidate_infections, 0)
AS candidate_infections,
COALESCE(d.device_days, 0)
AS device_days,
ROUND(
SAFE_DIVIDE(
COALESCE(i.candidate_infections, 0),
NULLIF(d.device_days, 0)
) * 1000,
2
) AS rate_per_1000_device_days
FROM `hospital_infections.month_unit_infections` AS m
LEFT JOIN `hospital_infections.monthly_infection_candidates` AS i
ON m.month = i.month
AND m.unit = i.unit
AND m.infection_type = i.infection_type
LEFT JOIN `hospital_infections.monthly_device_days` AS d
ON m.month = d.month
AND m.unit = d.unit
AND m.infection_type = d.infection_type
ORDER BY
m.month,
m.unit,
m.infection_type;
Forty-eight of the 72 reporting rows had zero analytical candidate events.
Keeping these zero-event rows was important for a complete monthly trend rather than displaying only months in which an event occurred.
SELECT
COUNT(*) AS total_audits,
SUM(bundle_compliant_flag) AS compliant_audits,
COUNT(*) - SUM(bundle_compliant_flag) AS noncompliant_audits,
ROUND(
SAFE_DIVIDE(
SUM(bundle_compliant_flag),
COUNT(*)
) * 100,
1
) AS overall_bundle_compliance_pct
FROM `hospital_infections.device_bundle_audits_final`;
Results:
1,368 audits
1,055 fully compliant
313 noncompliant
77.1% all-or-none compliance
SELECT
unit,
device_type,
infection_type,
COUNT(*) AS total_audits,
SUM(bundle_compliant_flag) AS compliant_audits,
COUNT(*) - SUM(bundle_compliant_flag) AS noncompliant_audits,
ROUND(
SAFE_DIVIDE(
SUM(bundle_compliant_flag),
COUNT(*)
) * 100,
1
) AS overall_bundle_compliance_pct
FROM `hospital_infections.device_bundle_audits_final`
GROUP BY
unit,
device_type,
infection_type
ORDER BY
unit,
device_type;
Results:
ICU — Central Line / CLABSI
Audits: 264
Compliant: 162
Compliance: 61.4%
ICU — Foley / CAUTI
Audits: 348
Compliant: 272
Compliance: 78.2%
Med-Surg — Central Line / CLABSI
Audits: 202
Compliant: 156
Compliance: 77.2%
Med-Surg — Foley / CAUTI
Audits: 337
Compliant: 271
Compliance: 80.4%
Telemetry — Central Line / CLABSI
Audits: 72
Compliant: 65
Compliance: 90.3%
Telemetry — Foley / CAUTI
Audits: 145
Compliant: 129
Compliance: 89.0%
The ICU was the lowest-compliance unit for both device types. The central-line difference was especially large.
SELECT
unit,
ROUND(
AVG(CASE
WHEN hand_hygiene_compliant = TRUE
THEN 1 ELSE 0
END) * 100,
1
) AS hand_hygiene_pct,
ROUND(
AVG(CASE
WHEN daily_necessity_review_completed = TRUE
THEN 1 ELSE 0
END) * 100,
1
) AS necessity_review_pct,
ROUND(
AVG(CASE
WHEN closed_system_maintained = 'Y'
THEN 1 ELSE 0
END) * 100,
1
) AS closed_system_pct,
ROUND(
AVG(CASE
WHEN drainage_bag_below_bladder = 'Y'
THEN 1 ELSE 0
END) * 100,
1
) AS bag_below_bladder_pct,
ROUND(
AVG(CASE
WHEN unobstructed_flow = 'Y'
THEN 1 ELSE 0
END) * 100,
1
) AS unobstructed_flow_pct
FROM `hospital_infections.device_bundle_audits_final`
WHERE device_type = 'Foley'
GROUP BY unit
ORDER BY unit;
Results:
ICU
Hand Hygiene: 97.1%
Daily Necessity Review: 93.7%
Closed System: 95.1%
Drainage Bag Below Bladder: 93.1%
Unobstructed Flow: 96.3%
Med-Surg
Hand Hygiene: 96.7%
Daily Necessity Review: 92.6%
Closed System: 95.8%
Drainage Bag Below Bladder: 96.7%
Unobstructed Flow: 96.1%
Telemetry
Hand Hygiene: 99.3%
Daily Necessity Review: 95.9%
Closed System: 97.9%
Drainage Bag Below Bladder: 97.9%
Unobstructed Flow: 97.9%
SELECT
unit,
ROUND(
AVG(CASE
WHEN hand_hygiene_compliant = TRUE
THEN 1 ELSE 0
END) * 100,
1
) AS hand_hygiene_pct,
ROUND(
AVG(CASE
WHEN daily_necessity_review_completed = TRUE
THEN 1 ELSE 0
END) * 100,
1
) AS necessity_review_pct,
ROUND(
AVG(CASE
WHEN dressing_clean_dry_intact = 'Y'
THEN 1 ELSE 0
END) * 100,
1
) AS dressing_pct,
ROUND(
AVG(CASE
WHEN hub_disinfection_compliant = 'Y'
THEN 1 ELSE 0
END) * 100,
1
) AS hub_disinfection_pct,
ROUND(
AVG(CASE
WHEN chg_bathing_completed = 'Y'
THEN 1 ELSE 0
END) * 100,
1
) AS chg_bathing_pct
FROM `hospital_infections.device_bundle_audits_final`
WHERE device_type = 'Central Line'
GROUP BY unit
ORDER BY unit;
Results:
ICU
Hand Hygiene: 91.7%
Daily Necessity Review: 91.3%
Dressing Clean/Dry/Intact: 91.3%
Hub Disinfection: 89.0%
CHG Bathing: 87.9%
Med-Surg
Hand Hygiene: 96.5%
Daily Necessity Review: 94.1%
Dressing Clean/Dry/Intact: 95.0%
Hub Disinfection: 95.0%
CHG Bathing: 94.1%
Telemetry
Hand Hygiene: 98.6%
Daily Necessity Review: 95.8%
Dressing Clean/Dry/Intact: 95.8%
Hub Disinfection: 98.6%
CHG Bathing: 100.0%
CHG bathing and hub disinfection were the two lowest-performing ICU central-line elements.
The larger point, however, was that several individual elements could perform near or above 90% while all-or-none compliance remained much lower. A failure in any applicable element caused the complete bundle to fail.
CREATE OR REPLACE TABLE
`hospital_infections.monthly_bundle_compliance` AS
SELECT
audit_month AS month,
unit,
infection_type,
COUNT(*) AS total_audits,
SUM(bundle_compliant_flag) AS compliant_audits,
ROUND(
SAFE_DIVIDE(
SUM(bundle_compliant_flag),
COUNT(*)
) * 100,
1
) AS bundle_compliance_pct
FROM `hospital_infections.device_bundle_audits_final`
GROUP BY
month,
unit,
infection_type
ORDER BY
month,
unit,
infection_type;
The process table contained 71 of the possible 72 month × unit × infection-type combinations.
The missing process combination was December 2024 Telemetry CLABSI, where there were also zero central-line days. Its process values were retained as NULL after the later join.
CREATE OR REPLACE TABLE
`hospital_infections.tableau_quality_fact` AS
SELECT
i.month,
i.unit,
i.infection_type,
i.candidate_infections,
i.device_days,
i.rate_per_1000_device_days,
b.total_audits,
b.compliant_audits,
b.bundle_compliance_pct
FROM `hospital_infections.tableau_infection_fact` AS i
LEFT JOIN `hospital_infections.monthly_bundle_compliance` AS b
ON i.month = b.month
AND i.unit = b.unit
AND i.infection_type = b.infection_type
ORDER BY
i.month,
i.unit,
i.infection_type;
The infection table remained the base table so all 72 reporting combinations were preserved.
CREATE OR REPLACE TABLE
`hospital_infections.analysis_unit_summary` AS
SELECT
unit,
infection_type,
SUM(candidate_infections) AS candidate_infections,
SUM(device_days) AS device_days,
ROUND(
SAFE_DIVIDE(
SUM(candidate_infections),
SUM(device_days)
) * 1000,
2
) AS rate_per_1000_device_days,
SUM(total_audits) AS total_audits,
SUM(compliant_audits) AS compliant_audits,
ROUND(
SAFE_DIVIDE(
SUM(compliant_audits),
SUM(total_audits)
) * 100,
1
) AS bundle_compliance_pct
FROM `hospital_infections.tableau_quality_fact`
GROUP BY
unit,
infection_type
ORDER BY
infection_type,
rate_per_1000_device_days DESC;
Ranking query:
SELECT
*,
RANK() OVER (
PARTITION BY infection_type
ORDER BY rate_per_1000_device_days DESC
) AS rate_rank_within_infection_type,
RANK() OVER (
PARTITION BY infection_type
ORDER BY bundle_compliance_pct ASC
) AS low_compliance_rank_within_infection_type
FROM `hospital_infections.analysis_unit_summary`
ORDER BY
infection_type,
rate_rank_within_infection_type;
The ICU ranked:
#1 CAUTI candidate rate and lowest CAUTI bundle compliance
#1 CLABSI candidate rate and lowest CLABSI bundle compliance
The CLABSI difference was substantially larger and therefore became the leading improvement signal.
Rather than storing each maintenance element in a separate Tableau calculation, the audit table was reshaped into a long format with one row per applicable element per audit.
CREATE OR REPLACE TABLE
`hospital_infections.analysis_bundle_elements` AS
SELECT
a.audit_id,
a.audit_month AS month,
a.unit,
a.device_type,
a.infection_type,
element.bundle_element,
element.is_compliant
FROM `hospital_infections.device_bundle_audits_final` AS a,
UNNEST([
STRUCT(
'Hand Hygiene' AS bundle_element,
a.hand_hygiene_compliant AS is_compliant,
TRUE AS applicable
),
STRUCT(
'Daily Necessity Review',
a.daily_necessity_review_completed,
TRUE
),
STRUCT(
'Closed System Maintained',
IF(
a.device_type = 'Foley',
a.closed_system_maintained = 'Y',
NULL
),
a.device_type = 'Foley'
),
STRUCT(
'Drainage Bag Below Bladder',
IF(
a.device_type = 'Foley',
a.drainage_bag_below_bladder = 'Y',
NULL
),
a.device_type = 'Foley'
),
STRUCT(
'Unobstructed Flow',
IF(
a.device_type = 'Foley',
a.unobstructed_flow = 'Y',
NULL
),
a.device_type = 'Foley'
),
STRUCT(
'Dressing Clean/Dry/Intact',
IF(
a.device_type = 'Central Line',
a.dressing_clean_dry_intact = 'Y',
NULL
),
a.device_type = 'Central Line'
),
STRUCT(
'Hub Disinfection',
IF(
a.device_type = 'Central Line',
a.hub_disinfection_compliant = 'Y',
NULL
),
a.device_type = 'Central Line'
),
STRUCT(
'CHG Bathing',
IF(
a.device_type = 'Central Line',
a.chg_bathing_completed = 'Y',
NULL
),
a.device_type = 'Central Line'
)
]) AS element
WHERE element.applicable = TRUE;
Validation:
6,840 element observations
1,368 audits
5 applicable elements per audit
0 NULL compliance results among applicable elements
CREATE OR REPLACE TABLE
`hospital_infections.analysis_device_cohort` AS
WITH candidate_devices AS (
SELECT
device_id,
COUNT(DISTINCT infection_id) AS candidate_infections
FROM `hospital_infections.infection_surveillance_final`
WHERE analytical_case_classification IN (
'Analytical CAUTI Candidate',
'Analytical CLABSI Candidate'
)
GROUP BY device_id
)
SELECT
d.device_id,
d.encounter_id,
e.patient_id,
d.insertion_unit_clean AS unit,
d.device_type,
d.insertion_date,
d.device_end_date_clean,
d.device_days,
CASE
WHEN d.device_days <= 2
THEN '1-2 Days'
WHEN d.device_days <= 4
THEN '3-4 Days'
WHEN d.device_days <= 7
THEN '5-7 Days'
ELSE '8+ Days'
END AS duration_band,
e.primary_diagnosis,
p.age,
p.sex,
p.diabetes_flag,
p.immunosuppressed_flag,
p.bmi_category,
COALESCE(c.candidate_infections, 0)
AS candidate_infections,
CASE
WHEN COALESCE(c.candidate_infections, 0) > 0
THEN 1
ELSE 0
END AS candidate_device_flag
FROM `hospital_infections.device_events_final` AS d
JOIN `hospital_infections.encounters_clean` AS e
ON d.encounter_id = e.encounter_id
JOIN `hospital_infections.patients_raw` AS p
ON e.patient_id = p.patient_id
LEFT JOIN candidate_devices AS c
ON d.device_id = c.device_id;
Final table:
865 device episodes
865 unique device IDs
31 candidate infections
CREATE OR REPLACE TABLE
`hospital_infections.analysis_device_duration_summary` AS
SELECT
unit,
device_type,
duration_band,
COUNT(*) AS device_episodes,
SUM(device_days) AS device_days,
SUM(candidate_device_flag) AS candidate_devices,
SUM(candidate_infections) AS candidate_infections,
ROUND(
SAFE_DIVIDE(
SUM(candidate_infections),
SUM(device_days)
) * 1000,
2
) AS candidates_per_1000_device_days
FROM `hospital_infections.analysis_device_cohort`
GROUP BY
unit,
device_type,
duration_band
ORDER BY
unit,
device_type,
CASE duration_band
WHEN '1-2 Days' THEN 1
WHEN '3-4 Days' THEN 2
WHEN '5-7 Days' THEN 3
WHEN '8+ Days' THEN 4
END;
Hospital-wide duration results:
Central lines
1–2 days
Device-Days: 146
Candidates: 0
Rate per 1,000: 0.00
3–4 days
Device-Days: 414
Candidates: 0
Rate per 1,000: 0.00
5–7 days
Device-Days: 511
Candidates: 7
Rate per 1,000: 13.70
8+ days
Device-Days: 424
Candidates: 8
Rate per 1,000: 18.87
Foley catheters
1–2 days
Device-Days: 189
Candidates: 0
Rate per 1,000: 0.00
3–4 days
Device-Days: 736
Candidates: 1
Rate per 1,000: 1.36
5–7 days
Device-Days: 935
Candidates: 10
Rate per 1,000: 10.70
8+ days
Device-Days: 557
Candidates: 5
Rate per 1,000: 8.98
Across the full dataset:
62.5% of central-line days occurred in 5+ day episodes.
61.7% of Foley days occurred in 5+ day episodes.
All 15 CLABSI candidates occurred in 5+ day central-line episodes.
15 of 16 CAUTI candidates occurred in 5+ day Foley episodes.
The duration bands are exploratory analytical groups, not clinical removal thresholds.
CREATE OR REPLACE TABLE
`hospital_infections.analysis_candidate_detail` AS
SELECT
i.infection_id,
i.encounter_id,
i.device_id,
i.unit_clean AS unit,
CASE
WHEN i.analytical_case_classification =
'Analytical CAUTI Candidate'
THEN 'CAUTI'
WHEN i.analytical_case_classification =
'Analytical CLABSI Candidate'
THEN 'CLABSI'
END AS infection_type,
i.culture_date,
DATE_TRUNC(i.culture_date, MONTH) AS month,
i.organism,
i.device_type,
i.insertion_date,
i.device_end_date_clean,
i.total_device_days AS device_days,
i.device_day_at_culture_proxy,
i.nhsn_criteria_met_raw,
e.primary_diagnosis,
p.age,
p.sex,
p.diabetes_flag,
p.immunosuppressed_flag,
p.bmi_category
FROM `hospital_infections.infection_surveillance_final` AS i
JOIN `hospital_infections.encounters_clean` AS e
ON i.encounter_id = e.encounter_id
JOIN `hospital_infections.patients_raw` AS p
ON e.patient_id = p.patient_id
WHERE i.analytical_case_classification IN (
'Analytical CAUTI Candidate',
'Analytical CLABSI Candidate'
);
Result: 31 candidate-event rows.
CREATE OR REPLACE TABLE
`hospital_infections.analysis_surveillance_review_summary` AS
SELECT
unit_clean AS unit,
COALESCE(device_type, 'No Linked Device')
AS device_type,
surveillance_review_status,
COUNT(*) AS surveillance_records
FROM `hospital_infections.infection_surveillance_review`
GROUP BY
unit,
device_type,
surveillance_review_status
ORDER BY
unit,
device_type,
surveillance_review_status;
This table supports the surveillance-review matrix and QA KPIs in the third Tableau dashboard.
CREATE OR REPLACE TABLE
`hospital_infections.tableau_quality_fact_final` AS
SELECT
*,
EXTRACT(YEAR FROM month) AS year,
EXTRACT(MONTH FROM month) AS month_number,
FORMAT_DATE('%b', month) AS month_name
CASE
WHEN total_audits IS NULL
THEN FALSE
ELSE TRUE
END AS audit_data_available,
CASE
WHEN candidate_infections > 0
THEN TRUE
ELSE FALSE
END AS candidate_event_month
FROM `hospital_infections.tableau_quality_fact`;
SELECT
infection_type,
organism,
COUNT(*) AS candidate_infections
FROM `hospital_infections.analysis_candidate_detail`
GROUP BY
infection_type,
organism
ORDER BY
infection_type,
candidate_infections DESC;
CAUTI candidates:
Klebsiella pneumoniae: 4
Candida albicans: 3
Enterococcus faecalis: 2
E. coli: 2
Staphylococcus aureus (MSSA): 2
Proteus mirabilis: 1
Pseudomonas aeruginosa: 1
Vancomycin-resistant Enterococcus: 1
CLABSI candidates:
Proteus mirabilis: 4
Candida albicans: 3
Pseudomonas aeruginosa: 3
Vancomycin-resistant Enterococcus: 2
Staphylococcus aureus (MSSA): 1
E. coli: 1
Enterococcus faecalis: 1
The SQL analysis shifted the project away from simply reporting infection counts.
The main story became the combination of outcome, process, exposure, and data quality.
First, ICU central lines had both the highest CLABSI candidate rate and the lowest complete bundle compliance. That made ICU central-line prevention the clearest starting point for a quality-improvement investigation.
Second, device duration provided another actionable area for review. Most device exposure occurred in episodes lasting at least five days, and nearly every analytical candidate was associated with these longer-duration groups. This supported a focus on device necessity and timely removal while avoiding the claim that duration itself caused infection.
Third, individual maintenance elements generally performed better than complete all-or-none bundle compliance. This showed that relatively small failures across several steps can accumulate into a much larger reliability problem when the complete process is evaluated.
Finally, surveillance QA remained part of the quality-improvement story. Thirty records required additional review and 33 could not be assessed for device association from the available data. Improving surveillance data capture and linkage is therefore an improvement opportunity alongside the clinical prevention processes themselves.