Re: Rewriting query to avoid inline view

[Rob Wultsch]

On Wed, Apr 23, 2008 at 5: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
>
>  Any tips greatly appreciated, thanks.
>
>  Morten

First off your porting over or dealing with formerly oracle code, right?

I am not sure if the above syntax is legal in mysql

Here is a shorter, more legal version of what you have above:
SELECT virtual_date_range.index_date AS index_date, COUNT(*) AS matches
FROM events, (
 SELECT DATE('2008-04-01') AS index_date UNION ALL
 SELECT DATE('2008-04-02') AS index_date UNION ALL
 SELECT DATE('2008-04-03') AS index_date
)  AS virtual_date_range
WHERE virtual_date_range.index_date BETWEEN events.start_date AND
events.end_date
GROUP BY index_date;

Here is a start for doing lots of dates

CREATE TABLE integers(i int NOT NULL PRIMARY KEY);
INSERT INTO integers(i) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

SELECT ADDDATE( CURDATE( ) , INTERVAL t.i *10 + u.iDAY )
FROM integers AS u, integers AS t
WHERE (t.i *10 + u.i ) <100;


-- 
Rob Wultsch
wultsch@stripped
wultsch (aim)