Is there a query in MySQL that can, given a table formatted as follows, give a count of the number of patients being treated at each hospital for each date within a range, such as a calendar year (i.e. output a count of distinct patient ids that were at the time admitted to each hospital on 2021-01-01, a count for 2021-01-02, etc.)?
The table does not have a specific date column.
| PatientID | HospitalName | AdmissionDate | DischargeDate |
|---|---|---|---|
| 002 | Sacred Cross | 2021-09-15 | 2021-09-22 |
| 003 | Sacred Heart | 2021-10-15 | 2021-12-04 |
| 004 | Sacred Cross | 2021-09-17 | NULL |
Easy if you have a table with all dates within the range you need.
(example of calendar table)
SELECT HospitalName, Calendar_Date, Count(PatientID) AS TotalPatients
FROM Ref_Calendar cal
LEFT JOIN YourTable t
ON cal.calendar_date BETWEEN t.AdmissionDate AND COALESCE(t.DischargeDate, '2239-06-30')
WHERE Calendar_Date BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY HospitalName, Calendar_Date