Daily Admissions With Quiet Days and a Running Total
The hospital's admissions chart must show every calendar day from the first admission date in the table to the last one, including quiet days with no…
- Difficulty: Hard
- Topics: Dates, Window Functions, Subqueries
- Dialect: MySQL
- Problem: #222
Problem statement
The hospital's admissions chart must show every calendar day from the first admission date in the table to the last one, including quiet days with no admission (which have no row in Admission).
Return day, admissions (admissions that day, 0 on a quiet day) and running_total (all admissions from the first day up to and including this one), ordered by day. An empty table gives no rows.
Tables
Table: Admission
| Column | Type |
|---|---|
| admission_id | int |
| patient_id | int |
| admit_date | date |
| ward | varchar |
Primary key: admission_id.
One row per inpatient admission. The first and last admission dates are never more than a month apart.
Examples
Example 1
Admission
| admission_id | patient_id | admit_date | ward |
|---|---|---|---|
| 1 | 101 | 2025-03-01 | General |
| 2 | 102 | 2025-03-01 | ICU |
| 3 | 103 | 2025-03-02 | General |
| 4 | 104 | 2025-03-05 | Maternity |
| 5 | 105 | 2025-03-05 | General |
| 6 | 106 | 2025-03-05 | Paediatric |
| 7 | 107 | 2025-03-07 | General |
Output
| day | admissions | running_total |
|---|---|---|
| 2025-03-01 | 2 | 2 |
| 2025-03-02 | 1 | 3 |
| 2025-03-03 | 0 | 3 |
| 2025-03-04 | 0 | 3 |
| 2025-03-05 | 3 | 6 |
| 2025-03-06 | 0 | 6 |
| 2025-03-07 | 1 | 7 |
How to solve Daily Admissions With Quiet Days and a Running Total
A GROUP BY over Admission can only produce days that have rows, so the quiet days have to be generated. A recursive CTE does that: the anchor is the first admission date, and each recursive step adds one day with DATE_ADD(day, INTERVAL 1 DAY) until the last admission date is reached. The table spans at most a month, so the recursion is short and always terminates.
LEFT JOIN the admissions onto the calendar on the date and group by day; COUNT(a.admission_id) counts 0 for a quiet day, where COUNT(*) would count the calendar row itself and say 1. The running total is a window sum over the days in order, with an explicit ROWS frame — the days are unique, so it is the same as the default here, but stating it avoids surprises.
On an empty table the anchor still returns one row, with a NULL day, and the recursion stops at once; WHERE day IS NOT NULL removes it so the answer is empty. The alternative uses correlated counts — admissions on the day and up to the day — instead of a join and a window.
Reference solution (MySQL)
WITH RECURSIVE days AS (
SELECT MIN(admit_date) AS day FROM Admission
UNION ALL
SELECT DATE_ADD(day, INTERVAL 1 DAY) FROM days
WHERE day < (SELECT MAX(admit_date) FROM Admission)
), daily AS (
SELECT d.day, COUNT(a.admission_id) AS admissions
FROM days d
LEFT JOIN Admission a ON a.admit_date = d.day
WHERE d.day IS NOT NULL
GROUP BY d.day
)
SELECT day, admissions,
SUM(admissions) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily
ORDER BY dayAnother way
WITH RECURSIVE days AS (
SELECT MIN(admit_date) AS day, MAX(admit_date) AS last_day FROM Admission
UNION ALL
SELECT DATE_ADD(day, INTERVAL 1 DAY), last_day FROM days WHERE day < last_day
)
SELECT d.day,
(SELECT COUNT(*) FROM Admission a WHERE a.admit_date = d.day) AS admissions,
(SELECT COUNT(*) FROM Admission a WHERE a.admit_date <= d.day) AS running_total
FROM days d
WHERE d.day IS NOT NULL
ORDER BY d.day← Double-Booked Doctor Appointment Slots · Employees Overdue for Probation Confirmation →