Ward Bed Occupancy on the Census Date — SQL Joins Problem
The midnight census for 2025-02-15 counts a bed as occupied by every admission with admit_date on or before 2025-02-15 and not discharged yet on that date —…
- Difficulty: Medium
- Topics: Joins, Dates, Aggregation
- Dialect: MySQL
- Problem: #212
Problem statement
The midnight census for 2025-02-15 counts a bed as occupied by every admission with admit_date on or before 2025-02-15 and not discharged yet on that date — discharge_date NULL or after 2025-02-15 (a patient discharged on the 15th has left).
Return every ward — including wards with no patient — with ward_name, beds, occupied and occupancy_pct = 100 × occupied ÷ beds, rounded to 2 decimals. Order by occupancy_pct descending, then ward_name.
Tables
Table: Ward
| Column | Type |
|---|---|
| ward_id | int |
| ward_name | varchar |
| beds | int |
Primary key: ward_id.
beds is the ward's sanctioned bed count, always at least 1.
Table: Admission
| Column | Type |
|---|---|
| admission_id | int |
| patient_id | int |
| ward_id | int |
| admit_date | date |
| discharge_date | date |
Primary key: admission_id.
discharge_date is NULL for patients still admitted.
Examples
Example 1
Ward
| ward_id | ward_name | beds |
|---|---|---|
| 1 | General | 10 |
| 2 | ICU | 4 |
| 3 | Maternity | 6 |
| 4 | Paediatric | 5 |
Admission
| admission_id | patient_id | ward_id | admit_date | discharge_date |
|---|---|---|---|---|
| 1 | 101 | 1 | 2025-02-10 | 2025-02-16 |
| 2 | 102 | 1 | 2025-02-15 | NULL |
| 3 | 103 | 1 | 2025-02-12 | 2025-02-15 |
| 4 | 104 | 2 | 2025-02-01 | NULL |
| 5 | 105 | 2 | 2025-02-14 | 2025-02-20 |
| 6 | 106 | 2 | 2025-02-16 | NULL |
| 7 | 107 | 3 | 2025-02-05 | 2025-02-08 |
| 8 | 108 | 1 | 2025-01-30 | NULL |
Output
| ward_name | beds | occupied | occupancy_pct |
|---|---|---|---|
| ICU | 4 | 2 | 50 |
| General | 10 | 3 | 30 |
| Maternity | 6 | 0 | 0 |
| Paediatric | 5 | 0 | 0 |
How to solve Ward Bed Occupancy on the Census Date
An admission occupies a bed on the census date when it started on or before that date and had not ended by it: admit_date <= '2025-02-15' and discharge_date either NULL or later than the 15th. That is the classic interval contains a point test, with the discharge day itself counted as free.
The wards with nobody in them are the trap. A LEFT JOIN from Ward keeps every ward, but only if the census conditions sit in the ON clause: written in WHERE they would be tested against the NULL columns of an unmatched ward and remove it. With the conditions in ON, an empty ward survives with one row of NULLs, and COUNT(a.admission_id) — which skips NULLs — counts 0 for it, while COUNT(*) would wrongly say 1.
The percentage is 100 × occupied ÷ beds, rounded to 2 decimals. Alternatives: a correlated COUNT(*) subquery per ward, or a plain LEFT JOIN on the ward with the census test moved into a conditional SUM. All read the admissions once per ward or once overall.
Reference solution (MySQL)
SELECT w.ward_name, w.beds, COUNT(a.admission_id) AS occupied,
ROUND(100 * COUNT(a.admission_id) / w.beds, 2) AS occupancy_pct
FROM Ward w
LEFT JOIN Admission a
ON a.ward_id = w.ward_id
AND a.admit_date <= '2025-02-15'
AND (a.discharge_date IS NULL OR a.discharge_date > '2025-02-15')
GROUP BY w.ward_id, w.ward_name, w.beds
ORDER BY occupancy_pct DESC, w.ward_nameAnother way
SELECT ward_name, beds, occupied, ROUND(occupied * 100 / beds, 2) AS occupancy_pct FROM (
SELECT w.ward_name, w.beds,
(SELECT COUNT(*) FROM Admission a WHERE a.ward_id = w.ward_id AND a.admit_date <= '2025-02-15' AND COALESCE(a.discharge_date, '9999-12-31') > '2025-02-15') AS occupied
FROM Ward w
) t ORDER BY occupancy_pct DESC, ward_nameAnother way
SELECT w.ward_name, w.beds, SUM(CASE WHEN a.admit_date <= '2025-02-15' AND (a.discharge_date IS NULL OR a.discharge_date > '2025-02-15') THEN 1 ELSE 0 END) AS occupied,
ROUND(100 * SUM(CASE WHEN a.admit_date <= '2025-02-15' AND (a.discharge_date IS NULL OR a.discharge_date > '2025-02-15') THEN 1 ELSE 0 END) / w.beds, 2) AS occupancy_pct
FROM Ward w LEFT JOIN Admission a ON a.ward_id = w.ward_id
GROUP BY w.ward_id, w.ward_name, w.beds
ORDER BY occupancy_pct DESC, w.ward_name← Lab Tests Slower Than Their Usual Turnaround · Diabetic Patients Whose Latest HbA1c Improved →