PatientLT Applied — Standalone

## The scenario You are a Data Quality Analyst embedded in the Trust's Information Governance team. NHS England has notified the Trust that a data quality review begins in eight weeks, focusing on patient-pathway integrity (are admissions properly closed?), RTT compliance (are 18-week targets being tracked accurately?), and clinical coding completeness (are diagnoses coded to ICD-10?). Each finding becomes a remediation ticket. Each ticket needs an owner. Your SQL is the evidence. ## Dataset The full PatientLT schema — Patient, Hospital, Department (self-referencing), Staff (self-referencing), Admission, Appointment, Diagnosis, WaitingList, Trust. Realistic data-quality flaws are deliberately planted to mirror real NHS inspection findings: open admissions with no discharge, admissions in deactivated departments, RTT breaches, DNA appointments with no reschedule, NULL ICD-10 codes. ## Deliverables Twelve outputs across six investigation themes, plus one reusable View: **A. Joins & enrichment** — active patient directory, never-admitted patients, attendance summary by department. **B. Orphan detection** — admissions in deactivated departments, appointments linked to terminated staff. **C. Duplicate detection** — multiple open admissions per patient, duplicate registrations by name+DOB. **D. Missing relationships** — waiting-list patients with no follow-up appointment, admissions with no diagnosis, DNAs with no reschedule. **E. Reconciliation** — RTT breach report, attendance-vs-diagnosis quadrant reconciliation. **F. View** — `vw_PatientAuditCard` with per-patient quality flags + three queries against it. Every query is preceded by the four-line Investigation Framework header (Entity / Relationship / Question / Pattern). ## Acceptance criteria (summary) Investigation Framework header present and accurate · correct JOIN type (no INNER where LEFT is required) · deliberate NULL handling (no silent row drops) · row counts within expected ranges (the brief specifies ranges per output for sanity-checking) · view F1 reusable and deterministic. Defence is a 30-minute live code review focusing on JOIN reasoning, cardinality, and how query design reflects the business question. Full brief, ERD, and expected row-count ranges appear inside the lesson once enrolled.