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.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-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
tem****ary table to hold the values while you iterate through the loop.
|
