PatientLT Pro — Standalone

## The scenario You are embedded in the Trust's Business Intelligence team, reporting to the Head of Analytics. The Medical Director has commissioned a quarterly Clinical Performance Dashboard with six panes: full department hierarchy with live patient census, a parameterised performance report by department, length-of-stay percentiles by specialty, cumulative monthly admission/discharge trends, top diagnoses with readmission-risk scoring, and 30-day readmission gap detection. Each pane is a separate deliverable. You design the SQL, document the trade-offs, and defend every design decision. ## Deliverables **Pane 1.** Foundation View (`vw_ClinicalMasterView`) referenced by every downstream pane, plus a recursive CTE over the Department hierarchy with live patient census at each node. **Pane 2.** Parameterised stored procedure `usp_DepartmentPerformanceReport` returning three result sets (admission summary, appointment summary, waiting-list summary) from a named CTE chain. **Pane 3.** Window-function trio — NTILE(4) LOS quartiles partitioned by specialty, RANK/DENSE_RANK for longest stays per department, ROW_NUMBER for deterministic latest-admission-per-patient. **Pane 4.** Idempotent temp-table pipeline producing 24-month cumulative admission and discharge running totals with a date-spine. **Pane 5.** Top-5 ICD-10 diagnoses per department with readmission-risk scoring — delivered twice (subquery version vs CTE version) for side-by-side comparison. **Pane 6.** 30-day readmission gap detection using LAG over (PatientID ORDER BY AdmissionDate). **Pane 7.** Performance rewrite of a legacy DNA-report query identifying five issues (SELECT *, comma-join, non-sargable date filter, DISTINCT misuse, correlated MAX subquery) with annotated fixes. ## Acceptance criteria (summary) Every CTE named for what it computes — no t1/t2/x · window functions partitioned deliberately · recursive CTE has explicit anchor and recursive members with terminating `OPTION (MAXRECURSION N)` · stored procedure parameters carry defaults and inline comments · temp-table pipeline idempotent (DROP TABLE IF EXISTS) · date filters sargable · performance rewrite addresses all five named issues · the foundation View is referenced (not re-implemented) by at least three downstream panes. Defence is a 45-minute code review. The assessor picks two panes and walks them line-by-line — every CTE name, every PARTITION clause, every JOIN type must be defensible. Full brief, ERD, and the legacy query you must rewrite appear inside the lesson once enrolled.