Re: Rewriting query to avoid inline view

[Sebastian Mendel]

Baron Schwartz schrieb:
> Hi,
> 
> On Wed, Apr 23, 2008 at 8:42 AM, Morten Primdahl <primdahl@stripped> 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/

i knew that you would answer this ... ;-)

-- 
Sebastian Mendel