Avoiding an inline view using DML only

From: Morten <usenet_at_kikobu.com>
Date: Wed, 23 Apr 2008 05:22:46 -0700 (PDT)
Message-ID: <cdb348c2-4914-41d8-b9a3-4be3c61d375d_at_c58g2000hsc.googlegroups.com>

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.

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

Any tips greatly appreciated, thanks.

Morten Received on Wed Apr 23 2008 - 14:22:46 CEST