This is a common problem and a common solution is to have a table with
integers from zero to some large number (32,767 is common) as
described at http://sqlserver2000.databases.aspfa...ers-table.html
Although the SQL statements are specific to MS SQL Server, they can be
adapted to other RDBMS.

Here is part of the article:
Generating date ranges

When you need to generate a set of dates in a range, the typical
solution is to create a loop and iterate through the range, adding a
day each time. However, a numbers table can help us generate this
range as a set, instead of treating each date in the range
individually. This way, you can use the code directly in a subquery or
in a table-valued function, without having to worry about creating a
temporary table to hold the values while you iterate through the loop.

This is a common problem and a common solution is to have a table with
integers from zero to some large number (32,767 is common) as
described at http://sqlserver2000.databases.aspfa...ers-table.html
Although the SQL statements are specific to MS SQL Server, they can be
adapted to other RDBMS.

Here is part of the article:
Generating date ranges

When you need to generate a set of dates in a range, the typical
solution is to create a loop and iterate through the range, adding a
day each time. However, a numbers table can help us generate this
range as a set, instead of treating each date in the range
individually. This way, you can use the code directly in a subquery or
in a table-valued function, without having to worry about creating a
temporary table to hold the values while you iterate through the loop.

This is a common problem and a common solution is to have a table with
integers from zero to some large number (32,767 is common) as
described at http://sqlserver2000.databases.aspfa...ers-table.html
Although the SQL statements are specific to MS SQL Server, they can be
adapted to other RDBMS.

Here is part of the article:
Generating date ranges

When you need to generate a set of dates in a range, the typical
solution is to create a loop and iterate through the range, adding a
day each time. However, a numbers table can help us generate this
range as a set, instead of treating each date in the range
individually. This way, you can use the code directly in a subquery or
in a table-valued function, without having to worry about creating a
temporary table to hold the values while you iterate through the loop.

This is a common problem and a common solution is to have a table with
integers from zero to some large number (32,767 is common) as
described at http://sqlserver2000.databases.aspfa...ers-table.html
Although the SQL statements are specific to MS SQL Server, they can be
adapted to other RDBMS.

Here is part of the article:
Generating date ranges

When you need to generate a set of dates in a range, the typical
solution is to create a loop and iterate through the range, adding a
day each time. However, a numbers table can help us generate this
range as a set, instead of treating each date in the range
individually. This way, you can use the code directly in a subquery or
in a table-valued function, without having to worry about creating a
temporary table to hold the values while you iterate through the loop.

This is a common problem and a common solution is to have a table with
integers from zero to some large number (32,767 is common) as
described at http://sqlserver2000.databases.aspfa...ers-table.html
Although the SQL statements are specific to MS SQL Server, they can be
adapted to other RDBMS.

Here is part of the article:
Generating date ranges

When you need to generate a set of dates in a range, the typical
solution is to create a loop and iterate through the range, adding a
day each time. However, a numbers table can help us generate this
range as a set, instead of treating each date in the range
individually. This way, you can use the code directly in a subquery or
in a table-valued function, without having to worry about creating a
temporary table to hold the values while you iterate through the loop.

This is a common problem and a common solution is to have a table with
integers from zero to some large number (32,767 is common) as
described at http://sqlserver2000.databases.aspfa...ers-table.html
Although the SQL statements are specific to MS SQL Server, they can be
adapted to other RDBMS.

Here is part of the article:
Generating date ranges

When you need to generate a set of dates in a range, the typical
solution is to create a loop and iterate through the range, adding a
day each time. However, a numbers table can help us generate this
range as a set, instead of treating each date in the range
individually. This way, you can use the code directly in a subquery or
in a table-valued function, without having to worry about creating a
temporary table to hold the values while you iterate through the loop.

"Carl Federl" <cfederl (AT) yahoo (DOT) com> wrote

My tip is similar to yours, except that, instead of a numbers table, you
can have a table of dates. Let's call it an "almanac". The key is, of
course, a date, and it has one entry for each
day in the expected range. Other columns can be features of that day that
can be a relative nuisance to compute, such as whether the date is a
workday, a weekend day, or a holiday for the enterprise in question.
Populating this table involves writing a program to do it. Ten years worth
of dates is only 3,650 rows, plus a few for leap years.

create table almanac
(adate date,
other columns not relevant to this issue);

Now, a suitable join between almanac and events, with a restriction to
almanac dates between the low end and high end dates supplied by the user,
can solve the problem. If necessary, I can work out the suitable join.
Maybe the OP can work it out.



This technique is widely used in data warehouses to avoid putting messy
calendar computations in hundreds of queries.

"Carl Federl" <cfederl (AT) yahoo (DOT) com> wrote

My tip is similar to yours, except that, instead of a numbers table, you
can have a table of dates. Let's call it an "almanac". The key is, of
course, a date, and it has one entry for each
day in the expected range. Other columns can be features of that day that
can be a relative nuisance to compute, such as whether the date is a
workday, a weekend day, or a holiday for the enterprise in question.
Populating this table involves writing a program to do it. Ten years worth
of dates is only 3,650 rows, plus a few for leap years.

create table almanac
(adate date,
other columns not relevant to this issue);

Now, a suitable join between almanac and events, with a restriction to
almanac dates between the low end and high end dates supplied by the user,
can solve the problem. If necessary, I can work out the suitable join.
Maybe the OP can work it out.



This technique is widely used in data warehouses to avoid putting messy
calendar computations in hundreds of queries.

"Carl Federl" <cfederl (AT) yahoo (DOT) com> wrote

My tip is similar to yours, except that, instead of a numbers table, you
can have a table of dates. Let's call it an "almanac". The key is, of
course, a date, and it has one entry for each
day in the expected range. Other columns can be features of that day that
can be a relative nuisance to compute, such as whether the date is a
workday, a weekend day, or a holiday for the enterprise in question.
Populating this table involves writing a program to do it. Ten years worth
of dates is only 3,650 rows, plus a few for leap years.

create table almanac
(adate date,
other columns not relevant to this issue);

Now, a suitable join between almanac and events, with a restriction to
almanac dates between the low end and high end dates supplied by the user,
can solve the problem. If necessary, I can work out the suitable join.
Maybe the OP can work it out.



This technique is widely used in data warehouses to avoid putting messy
calendar computations in hundreds of queries.

"Carl Federl" <cfederl (AT) yahoo (DOT) com> wrote

My tip is similar to yours, except that, instead of a numbers table, you
can have a table of dates. Let's call it an "almanac". The key is, of
course, a date, and it has one entry for each
day in the expected range. Other columns can be features of that day that
can be a relative nuisance to compute, such as whether the date is a
workday, a weekend day, or a holiday for the enterprise in question.
Populating this table involves writing a program to do it. Ten years worth
of dates is only 3,650 rows, plus a few for leap years.

create table almanac
(adate date,
other columns not relevant to this issue);

Now, a suitable join between almanac and events, with a restriction to
almanac dates between the low end and high end dates supplied by the user,
can solve the problem. If necessary, I can work out the suitable join.
Maybe the OP can work it out.



This technique is widely used in data warehouses to avoid putting messy
calendar computations in hundreds of queries.