|
|
Posted by rola on 10/22/05 07:35
Thank you both for your advise!!
I think i got myself a little bit confused... As I always use the
tables created by our DBA, I am not very familiar with
creating/altering tables, as well as the primary key (sorry). Here is
what i do now every month:
--At the end of September
select A, B (A B would change every month)
into customer_20050930
from table_A (already in our database and will be updated monthly)
group by A, B
--At the end of October
select A, B (A B would change every month)
into customer_20051031
from table_A (already in our database and will be updated monthly)
group by A, B
I am wondering how I can apply what you suggested to my current query?
Could you please show me the query?
Many thanks!
Hugo Kornelis wrote:
> 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)
Navigation:
[Reply to this message]
|