On Wed, Apr 23, 2008 at 5: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
>
> 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
[EMAIL PROTECTED]
wultsch (aim)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
