SQL for Hospital Operations: Bed Occupancy, Length of Stay and Readmissions

<p>Tamarind Grove Hospital, a 190-bed multi-speciality hospital in Hyderabad, has its quarterly board review on 9 October. The surgeons want thirty more beds, the medical superintendent thinks stays are getting longer, and the quality committee wants to know how many patients come back within a month. Nikhil Varma, the hospital's new operations analyst, decides to answer all three from the admissions system itself: how full are the wards really, how long do patients stay, and who comes back?</p><p>Across five lessons you build the answers with him, in the hospital's own database, in an SQL lab in your browser: month filters that respect timestamps, cancelled registrations and day-care sessions kept out of inpatient figures, LEFT JOINs that keep a department with no admissions, length of stay counted in midnights with DATEDIFF, length-of-stay bands with CASE, bed-days clipped to the month with LEAST and GREATEST, patients still in a bed (a NULL discharge) counted rather than dropped, a midnight census built on a recursive calendar, 30-day emergency readmissions with EXISTS, bounce-backs with LAG() OVER (PARTITION BY ...), and a department league table. Every lesson you write the query yourself first, then hand the same job to your AI (ChatGPT, Claude, Copilot or Gemini) and learn exactly where it goes wrong. In the capstone your AI writes the board pack from one brief, and you prove every number before it reaches the board.</p>

Tools: SQL (MySQL), LEFT JOIN and conditional aggregation, DATEDIFF, LEAST and GREATEST, Recursive CTEs, EXISTS and window functions (LAG), Hospital KPIs: ALOS, occupancy, readmissions, AI assistants for SQL