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

Hi,

On Wed, Apr 23, 2008 at 8:42 AM, Morten Primdahl <primdahl@mac.com> 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...ntegers-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/

On Wed, Apr 23, 2008 at 5:42 AM, Morten Primdahl <primdahl@mac.com> 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@gmail.com
wultsch (aim)

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

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

--
Sebastian Mendel

On Wed, Apr 23, 2008 at 6:31 AM, Sebastian Mendel
<lists@sebastianmendel.de> wrote:
> Baron Schwartz schrieb:
> > SQL magic
> i knew that you would answer this ... ;-)

And he did it a minute or so faster than me... (though I did rip off
his integers table way back when)

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

Thanks Rob and Baron, I'd never heard of the integers table approach
before, really good stuff!

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

Nah, I just learned SQL on Oracle back in the day. DUAL works under
MySQL also - don't know since what revision, but it works on 5.0.45 at
least - but seeing that I don't need it, I'll stop using it, thanks
for the tip!

Morten

On Wed, Apr 23, 2008 at 6:39 AM, Morten Primdahl <primdahl@mac.com> wrote:
>
> Thanks Rob and Baron, I'd never heard of the integers table approach
> before, really good stuff!

If memory serves postgres has something similar built in, so the
syntax is something like
seq(1..100) or something like that (I can't remember the function name
for the life of me).

--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)

<schizophrenic>
Hi Rob,
On Wed, Apr 23, 2008 at 6:47 AM, Rob Wultsch <wultsch@gmail.com> wrote:
> On Wed, Apr 23, 2008 at 6:39 AM, Morten Primdahl <primdahl@mac.com> wrote:
> >
> > Thanks Rob and Baron, I'd never heard of the integers table approach
> > before, really good stuff!
>
> If memory serves postgres has something similar built in, so the
> syntax is something like
> seq(1..100) or something like that (I can't remember the function name
> for the life of me).

What you were thinking of is generate_series (
http://www.postgresql.org/docs/8.3/s...tions-srf.html ) .
</schizophrenic>
--
Rob Wultsch
wultsch@gmail.com
wultsch (aim)