Ward Bed Occupancy on the Census Date

Medium
212.

Ward Bed Occupancy on the Census Date

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

ColumnType
ward_idint
ward_namevarchar
bedsint

Primary key: ward_id.

beds is the ward's sanctioned bed count, always at least 1.

Table: Admission

ColumnType
admission_idint
patient_idint
ward_idint
admit_datedate
discharge_datedate

Primary key: admission_id.

discharge_date is NULL for patients still admitted.

Example

The rows must come back in the order shown.

Ward

ward_idward_namebeds
1General10
2ICU4
3Maternity6
4Paediatric5

Admission

admission_idpatient_idward_idadmit_datedischarge_date
110112025-02-102025-02-16
210212025-02-15NULL
310312025-02-122025-02-15
410422025-02-01NULL
510522025-02-142025-02-20
610622025-02-16NULL
710732025-02-052025-02-08
810812025-01-30NULL

Output

ward_namebedsoccupiedoccupancy_pct
ICU4250
General10330
Maternity600
Paediatric500
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
ward_idint
ward_namevarchar
bedsint

Primary key: ward_id.

beds is the ward's sanctioned bed count, always at least 1.

Table: Admission

ColumnType
admission_idint
patient_idint
ward_idint
admit_datedate
discharge_datedate

Primary key: admission_id.

discharge_date is NULL for patients still admitted.

Examples

Example 1

Ward

ward_idward_namebeds
1General10
2ICU4
3Maternity6
4Paediatric5

Admission

admission_idpatient_idward_idadmit_datedischarge_date
110112025-02-102025-02-16
210212025-02-15NULL
310312025-02-122025-02-15
410422025-02-01NULL
510522025-02-142025-02-20
610622025-02-16NULL
710732025-02-052025-02-08
810812025-01-30NULL

Output

ward_namebedsoccupiedoccupancy_pct
ICU4250
General10330
Maternity600
Paediatric500

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_name

Another 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_name

Another 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 →