0

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
Michael
  • 13
  • 1
  • what does `select version();` show? – ysth Dec 06 '21 at 18:12
  • does DischargeDate null mean not yet discharged? if AdmissionDate can be null, what does that mean? – ysth Dec 06 '21 at 18:13
  • Discharge date means not yet discharged, and so should be counted. Version is 5.7.30. Thanks for your help. EDIT: AdmissionDate cannot be null. – Michael Dec 06 '21 at 18:20

1 Answers1

0

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
LukStorms
  • 28,916
  • 5
  • 31
  • 45
  • Thank you LukStorms, this was very helpful. One issue I seem to be having is the count of null patients (i.e. patients that have not yet been discharged). It doesn't seem that this script counts them -- am I just missing something, or is there some way I can add this? – Michael Dec 06 '21 at 19:55
  • @Michael change t.DischargeDate to `coalesce(t.DischargeDate, '2021-12-07')` or whatever date you want to count them through, e.g. `9999-12-31` – ysth Dec 06 '21 at 20:21
  • hmm, well.. depends how long you want to count those with NULL DischargeDate. I guess `... OR DischargeDate IS NULL)` might be counting to much? But @ysth coalesce trick should work. I would just use `cal.calendar_date BETWEEN t.AdmissionDate AND COALESCE(DischargeDate, CURRENT_DATE)` – LukStorms Dec 06 '21 at 20:48
  • using current_date or now is almost always a bad idea; it makes the assumption that the timezone of your database session is what you expect; much better make the client supply the date it wants it to be – ysth Dec 06 '21 at 23:15
  • In this case such timezone difference won't matter as long the range in the WHERE clause is lower than today. And even if that includes today, it would only mean that today's counts could be wrong. – LukStorms Dec 06 '21 at 23:32
  • the example doesn't lead me to think that :) – ysth Dec 06 '21 at 23:34
  • Oh well, Michael can just set it to the next doomsday in 2239 then. – LukStorms Dec 06 '21 at 23:39
  • Thank you both for all of the help. I am still experiencing a few issues here and there, but I can chalk that up to my relative inexperience with MySQL, and both of your contributions have helped me immensely. – Michael Dec 07 '21 at 13:57