Hi, On Wed, Apr 23, 2008 at 8:42 AM, Morten Primdahl <[EMAIL PROTECTED]> wrote: > > Hi, > > 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 > matches > 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: > http://www.pastie.org/185419
You can generate the values with the integers table. http://www.xaprb.com/blog/2005/12/07/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 To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]