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
|
