Posted by Hugo Kornelis on 10/21/05 23:07

On 21 Oct 2005 10:24:10 -0700, rong.guo@gmail.com wrote:

>Greetings!
>
>I am now doing one type of analysis every month, and wanted to creat
>table names in a more efficient way.
>
>Here is what happens now, everytime I do the analysis, I will create a
>table called something like customer_20050930, and then update the
>table by using several update steps. Then next month I will create a
>table called customer_20051031. Does anyone know if there is a better
>way to do it?

Hi rong.guo,

Yes: add a column "Month" (or, better yet, two columns PeriodStart and
PeriodEnd) to your table, and add it to the primary key. I.e. if the
current primary key definition is

ALTER TABLE xxx
ADD PRIMARY KEY (DivisionID, ProductID)

you'll change it to

ALTER TABLE xxx
ADD PRIMARY KEY (PeriodStart, DivisionID, ProductID)
ALTER TABLE xxx
ADD UNIQUE (DivisionID, ProductID, PeriodEnd)

Then, in your code, add variables to hold PeriodStart and PeriodEnd and
use them in your queries. Next month, you'll only have to change the
values assigned to @PeriodStart and @PeriodEnd at the start of the
script.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]