Who asked: the quality director at Cedar Ridge Health, a fictional health system with two hospitals.
Why: the quality committee reviews readmissions every half-year and wants one report it can trust: how many inpatient stays there were, how many patients came back unplanned within 30 days, and where.
The data: hd_encounters (inpatient stays with admit and discharge dates, department, facility, payer and the planned flag) and hd_payers, January to June 2026, pulled on 1 July 2026.
What done looks like: the index stays with their next admission, the overall rate with planned readmissions excluded, the count that was set aside, the rate by department, by payer type and by month of discharge, and a short list of what the figures can and cannot say.
This project builds the report the quality director asked for, one figure at a time. Every step uses the same definition:
- Index stays: ended inpatient stays, January to June 2026.
- Readmission: the patient's next inpatient admission starts within 30 days of the index discharge and is not planned.
- Rate: readmissions over index stays, as a percentage rounded to 1 decimal place.
Each step is a query of its own: start from the stays CTE with LEAD(admit_date) and LEAD(planned), and change only the grouping. The last step is about what the figures can and cannot be used for.