This page consolidates the SQL Query Library and Power BI findings into one place, organized by the 8 original business questions. Each question shows the validated SQL query, the key finding, and which dashboard page answers it.

Data source tables: patient_visits (2,000 rows), patient_outcomes (2,000 rows), staff_shifts (300 rows), lookup_triage (5 rows). Full schema documented in the SQL library below.

Two known data limitations, flagged upfront:

• No explicit "boarding" (bed-wait) timestamp exists in this dataset — Q3 was answered differently than originally scoped, using a breach-vs-non-breach stage comparison instead.
• attending_staff_id doesn't reliably map a visit to its actual staffing shift (~0.5% date match rate) — Q5's correlation is built at the (site, shift period) level, giving only 9 data points. Treat as directional, not statistically robust.

📎 Power BI file: Northern_Health_Trust_PowerBI.pbix — attach this directly to this page from your computer (Notion integrations can't pull binary files from external sources automatically).

Q1 — What % of patients breach the four-hour A&E target, and how does this vary by site?

Dashboard: Executive Overview

SELECT
    v.trust_site,
    COUNT(*) AS total_attendances,
    SUM(o.breach_4hr_target) AS total_breaches,
    ROUND(100.0 * SUM(o.breach_4hr_target) / COUNT(*), 1) AS breach_rate_pct
FROM patient_visits v
JOIN patient_outcomes o ON v.visit_id = o.visit_id
GROUP BY v.trust_site
ORDER BY breach_rate_pct DESC;

Finding: Leeds General Infirmary has the highest breach rate at 9.45%, Royal Infirmary Leeds 8.33%, St James's University Hospital 7.22%. All three sites sit well below the 95% target. St James's has the best yearly average but the most volatile month-to-month performance; Leeds General is the most consistent day-to-day despite having the highest yearly average.

Q2 — Where in the patient journey is the most time lost, by site?

Dashboard: Patient Journey & Breach Drivers

SELECT
    trust_site,
    ROUND(AVG(EXTRACT(EPOCH FROM (triage_start_datetime - arrival_datetime)) / 60.0)::numeric, 1) AS avg_mins_wait_for_triage,
    ROUND(AVG(EXTRACT(EPOCH FROM (triage_end_datetime - triage_start_datetime)) / 60.0)::numeric, 1) AS avg_mins_triage_duration,
    ROUND(AVG(EXTRACT(EPOCH FROM (clinician_seen_datetime - triage_end_datetime)) / 60.0)::numeric, 1) AS avg_mins_wait_for_clinician,
    ROUND(AVG(EXTRACT(EPOCH FROM (treatment_start_datetime - clinician_seen_datetime)) / 60.0)::numeric, 1) AS avg_mins_wait_for_treatment,
    ROUND(AVG(EXTRACT(EPOCH FROM (treatment_end_datetime - treatment_start_datetime)) / 60.0)::numeric, 1) AS avg_mins_treatment_duration,
    ROUND(AVG(EXTRACT(EPOCH FROM (departure_datetime - treatment_end_datetime)) / 60.0)::numeric, 1) AS avg_mins_post_treatment_to_departure
FROM patient_visits
GROUP BY trust_site
ORDER BY trust_site;

Finding: Treatment duration (~70 min) and wait-for-clinician (~46-48 min) are by far the two biggest stages at every site, and nearly identical across all three sites (within 1-2 minutes). This is a systemic, Trust-wide bottleneck, not a single-site problem — a process fix would help all three sites at once.

Q3 — Which journey stage contributes most to four-hour breaches?

Dashboard: Patient Journey & Breach Drivers · Reframed from the original "boarding delay" question — no boarding field exists in this dataset.

SELECT
    po.breach_4hr_target,
    COUNT(*) AS n_visits,
    ROUND(AVG(EXTRACT(EPOCH FROM (pv.triage_start_datetime - pv.arrival_datetime)) / 60.0)::numeric, 1) AS avg_mins_wait_for_triage,
    ROUND(AVG(EXTRACT(EPOCH FROM (pv.triage_end_datetime - pv.triage_start_datetime)) / 60.0)::numeric, 1) AS avg_mins_triage_duration,
    ROUND(AVG(EXTRACT(EPOCH FROM (pv.clinician_seen_datetime - pv.triage_end_datetime)) / 60.0)::numeric, 1) AS avg_mins_wait_for_clinician,
    ROUND(AVG(EXTRACT(EPOCH FROM (pv.treatment_start_datetime - pv.clinician_seen_datetime)) / 60.0)::numeric, 1) AS avg_mins_wait_for_treatment,
    ROUND(AVG(EXTRACT(EPOCH FROM (pv.treatment_end_datetime - pv.treatment_start_datetime)) / 60.0)::numeric, 1) AS avg_mins_treatment_duration,
    ROUND(AVG(EXTRACT(EPOCH FROM (pv.departure_datetime - pv.treatment_end_datetime)) / 60.0)::numeric, 1) AS avg_mins_post_treatment_to_departure
FROM patient_visits pv
JOIN patient_outcomes po ON pv.visit_id = po.visit_id
GROUP BY po.breach_4hr_target
ORDER BY po.breach_4hr_target;