Hospital bed occupancy and ALOS from an admissions register

The question a medical superintendent actually asks

"Do we need more beds in Medicine, or are patients just staying too long?" BOR alone cannot answer that. A ward at 95% occupancy with a short ALOS is genuinely short of beds; the same 95% with a long ALOS may have a discharge problem — reports pending, a billing desk that closes at 6 pm, or TPA approvals that arrive the next morning. You need BOR, ALOS and bed turnover side by side, per ward.

Formulas, written so nobody can misread them

What the register usually looks like

The hospital information system export typically gives you two tables.

admissions: admission_id, uhid (patient ID), ward, admit_ts, discharge_ts (blank if still admitted), discharge_type (discharged, death, LAMA, transfer_out).

ward_beds: ward, functional_beds, sanctioned_beds.

Transfers deserve a warning now: some systems close one admission row and open another when a patient moves from ICU to a ward. Others keep one row and log movements in a separate table. Ask which before you count anything.

Worked example in SQLite (synthetic data)

The period is the seven days from 1 to 7 September 2026. Everything below is invented.

CREATE TABLE ward_beds (ward TEXT, functional_beds INT, sanctioned_beds INT);
INSERT INTO ward_beds VALUES ('Medicine', 8, 10), ('Surgery', 6, 6);

CREATE TABLE admissions (
  admission_id INT, uhid TEXT, ward TEXT,
  admit_ts TEXT, discharge_ts TEXT, discharge_type TEXT);
INSERT INTO admissions VALUES
 (1,'P01','Medicine','2026-08-29 21:10','2026-09-04 11:00','discharged'),
 (2,'P02','Medicine','2026-09-01 09:30','2026-09-06 13:15','discharged'),
 (3,'P03','Medicine','2026-09-02 16:00',NULL,NULL),
 (4,'P04','Medicine','2026-09-01 10:00','2026-09-08 10:00','discharged'),
 (5,'P05','Medicine','2026-09-03 23:40','2026-09-05 08:00','death'),
 (6,'P06','Medicine','2026-09-04 12:00','2026-09-04 18:00','discharged'),
 (7,'P07','Medicine','2026-08-25 08:00',NULL,NULL),
 (8,'P08','Surgery','2026-09-01 07:00','2026-09-03 17:00','discharged'),
 (9,'P09','Surgery','2026-09-02 08:00','2026-09-05 10:00','LAMA'),
 (10,'P10','Surgery','2026-09-05 06:00','2026-09-07 15:00','discharged');

-- Midnight census: a patient is counted on date d if admitted on or before d
-- and not discharged on or before d. Clip every stay to the period.
WITH params AS (SELECT '2026-09-01' AS p_start, '2026-09-07' AS p_end),
stay AS (
  SELECT a.*,
    MAX(date(admit_ts), p_start) AS s,
    MIN(COALESCE(date(discharge_ts), date(p_end, '+1 day')),
        date(p_end, '+1 day')) AS e,
    CASE WHEN date(discharge_ts) BETWEEN p_start AND p_end
         THEN 1 ELSE 0 END AS discharged_in_period
  FROM admissions a, params),
days AS (
  SELECT ward, discharged_in_period, admit_ts, discharge_ts,
    MAX(julianday(e) - julianday(s), 0) AS ip_days_in_period,
    julianday(date(discharge_ts)) - julianday(date(admit_ts)) AS full_stay
  FROM stay)
SELECT d.ward,
  SUM(ip_days_in_period) AS inpatient_days,
  b.functional_beds * 7 AS bed_days,
  ROUND(100.0 * SUM(ip_days_in_period) / (b.functional_beds * 7), 1) AS bor_pct,
  SUM(discharged_in_period) AS discharges,
  ROUND(SUM(CASE WHEN discharged_in_period = 1 THEN full_stay END) * 1.0
        / SUM(discharged_in_period), 2) AS alos_days,
  ROUND(SUM(discharged_in_period) * 1.0 / b.functional_beds, 2) AS turnover
FROM days d JOIN ward_beds b USING (ward)
GROUP BY d.ward;

The result:

Two details in that query matter. First, ALOS uses the whole stay of each patient discharged in the period (P01's stay began on 29 August, so all six nights count), while BOR uses only the days that fall inside the period. Mixing the two is the most common reason an ALOS "does not match" the occupancy report. Second, P04 left on 8 September, outside the period, so she contributes seven inpatient days to BOR but nothing to ALOS yet.

Where the numbers go wrong in hospitals

• Sanctioned beds in the denominator. Using Medicine's 10 sanctioned beds instead of 8 functional ones would report 42.9% instead of 53.6%. Licensing documents quote sanctioned beds; operations needs functional ones. Print both, label both.
• Day care hidden inside inpatient data. P06 was admitted and discharged on 4 September. Under midnight census she adds zero days but one discharge, which drags ALOS down. Many hospitals report day-care cases on a separate line for this reason. Decide once and write it in the report footnote.
• Long stayers disappear from ALOS. P07 has been in Medicine since 25 August and is still there. ALOS ignores her entirely until discharge. Add a "currently admitted more than 10 days" count so a bed-blocking patient is visible.
• Transfers counted twice. If ICU-to-ward moves create new admission rows, hospital-level discharges are inflated and hospital ALOS shrinks. Compute hospital totals from the first admit and final discharge per episode, and ward figures from movement rows.
• Timestamp time zones. Some HIS exports store timestamps in UTC. A patient admitted at 1:00 am IST on 2 September appears as 19:30 on 1 September in UTC, which adds a midnight that never happened. Convert to IST before taking date().

Presenting it to the medical superintendent

Lead with one sentence per ward that joins the three metrics: "Medicine ran at 54% occupancy this week with a 3.25-day average stay; one patient has been admitted for 13 nights and is not in the average." Then show a small table, not a dashboard. The superintendent will ask "compared with what?", so keep the previous four weeks beside this one, and say plainly when a ward is too small for a weekly figure to mean much — Surgery's 16.7% is based on seven inpatient days.

If occupancy is high and ALOS is rising, the next analysis is discharge-hour distribution: how many discharges happen after 4 pm. That points at process, which is cheaper to fix than beds.

The same admission-to-discharge logic appears in the capstone work of our Data Analyst programme, where learners build ward-level MIS from synthetic registers. If you are new to SQL date handling, the SQL date and time interview questions cover the functions used above.