Re: Avoiding an inline view using DML only

"Carl Federl" <cfederl_at_yahoo.com> wrote in message news:3f37a0da-3ea5-4b43-a4e8-ed98c6b05445_at_w74g2000hsh.googlegroups.com...
> 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
> temporary table to hold the values while you iterate through the loop.

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. Received on Thu Apr 24 2008 - 11:59:19 CEST