0

Let's assume I manage medical patient stays information system.

I want to get the patient count per day with the following minimal structure :

  • stay table has begin and end datetime columns
  • PHP gives me $first_day and $last_day limits

The following snippet is NOT what I want, since it only counts entries per day, and not present stays per day:

SELECT 
  DATE_FORMAT(`stay`.`begin`, '%Y-%m-%d') AS `date`, 
  COUNT(`stay`.`stay_id`) AS `total`
FROM `stay`
WHERE `stay`.`begin` <= '$first_day'
  AND `stay`.`end`   >= '$last_day'
GROUP BY `date` 
ORDER BY `date`

Last but not least, I'm looking for a full SQL query.

It goes without saying that making one SQL query for each day would be totally trivial.

Use of temporary (dates ?) table is clearly an option.

Tom Desp
  • 911
  • 6
  • 19

2 Answers2

1

A quick Google around leads me to this page: What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

What I would suggest is that you either follow the advice in that question, or construct your own loop in PHP.

Community
  • 1
  • 1
Gleeb
  • 166
  • 3
1

As you mentioned using a temporary table of all dates in the range you want is one way to handle this. If you created a table of date called foo with all dates between $first_day and $last_day inclusive (see here).

Then you can write your query like:

SELECT f.date, count(s.stay_id)
FROM foo f
JOIN stay s ON s.begin <= f.date AND s.end >= date
GROUP BY f.date
ORDER BY f.date
Community
  • 1
  • 1
GavinCattell
  • 3,863
  • 20
  • 22
  • Ok I feel kind of stupid now, I should have found (a) the query, (b) the stack thread you linked. Anyway, thanks a lot. – Tom Desp Mar 29 '12 at 12:04
  • One more thing, is casting my datetimes into dates necessary? I don't think so. – Tom Desp Mar 29 '12 at 12:06
  • In the select, or in the table? Additionally if you don't want a stored procedue you could also create a calendar table with all dates up until 2100, and just join that a use your begin/end. – GavinCattell Mar 29 '12 at 16:15
  • I meant in the select. For dates table, I was considering building it on demand, PHP side, but I guess having a dates table ready to use is much more efficient et prevents from using temporary tables. – Tom Desp Mar 31 '12 at 08:57