Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
From: Baron Schwartz (baronpercona.com)
Date: Wed Apr 23 2008 - 08:24:05 CDT
On Wed, Apr 23, 2008 at 8:42 AM, Morten Primdahl <primdahlmac.com> wrote:
> A user enters a date range (ie. 2 dates, '2008-04-01' and
> '2008-04-03'), the problem is to determine how many open events exist
> on each day in this interval.
> Assume that the "events" table has a "start_date" and an "end_date".
> One way to solve this problem, is to create an inline view in the
> query, eg.:
> SELECT virtual_date_range.index_date AS index_date, COUNT(*) AS
> FROM events, (
> SELECT DATE('2008-04-01') AS index_date FROM DUAL UNION ALL
> SELECT DATE('2008-04-02') FROM DUAL UNION ALL
> SELECT DATE('2008-04-03') FROM DUAL UNION ALL
> ) AS virtual_date_range
> WHERE virtual_date_range.index_date >= events.start_date
> AND virtual_date_range.index_date <= events.end_date
> GROUP BY index_date;
> This works. But I'm wondering if there's a more elegant way of
> expressing the same using pure DML, such that I don't need to build a
> huge inline view in case the range is multiple years. Anyone?
> A solution that doesn't return any rows for the dates that do not have
> an event would work.
> Example of the events table and the above query in action:
You can generate the values with the integers table.
Here's an example: http://markmail.org/message/6w46gyijsk5rrj4a
Baron Schwartz, Senior Consultant, Percona Inc.
Tel: +1 888 401 3401 ext 507
24/7 Emergency Line +1 888 401 3401 ext 911
Our Services: http://www.percona.com/services.html
Our Blog: http://www.mysqlperformanceblog.com/
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql