Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<8oqa61p4rgmhtcfh7rgsc8dup9pj1a9d1t@4ax.com>. ..
> On 18 Apr 2005 16:36:38 -0700, Bill wrote:
>
> >I would appreciate assistance developing a query that I haven't been
> >able to develop without using a second table.
> (snip)
> >I've previously beaten this
> >by building a table, simply a list of the dates for the first of each
> >month for the next ten years or so, and then selecting values based
> >upon a date selected from that table. However I'd be happier if I
> >could do it without the second table.
>
> Hi Bill,
>
> Why do you want to do it wothout a second table? Having a permanent
> auxiliary calendar table in your database is actually quite useful and I
> think that no database should ever be without one.
....
> This one cuts off the listing at the lowest number. If you increase the
> number (1) in the DATEADD expression, you can see that the number of
> open cases remains constant after the last month listed.
>
> Best, Hugo

Hugo;

Thank you for your assistance. I've reviewed the article you pointed
me to and have spent my spare time today building a number table and a
date table. I was reluctant to duplicate my earlier date table, which
only contained the dates for the first of the month into the SQL
Server environment since I felt that it was both cheating and
confusing. However the article showed that this can be a very useful
table and since it's been published I don't feel too bad about
emulating someone's work who know more about application development
than I do.

I do have one question, based mainly on my lack of formal training in
SQL Server and my experience this morning building the calendar table.
I was copying the code from the article and pasting it into SQL Query
Analyzer and running it, as I'm not certain where or how this code
should be executed. Most things ran very quickly after I modified
them properly to meet my environment. However the adding of row's to
the Calendar table (4,096) took several hours (2-3) I was really
surprised by this and wondered if I was doing something wrong but
since it finished successfully and subsequent code samples executed
quickly I moved on. My question is am I using the right part of the
SQL Server environment for this sort of work?

Thank you once again for your time and your assistance. Now that I've
overcome my reluctance to using the calendar table I'm comfortable
enough to go on and using you sample query to get what I was looking
for.

Thank you.

Cheers;

Bill