>Hi everybody,
>
>I have a statistics table with this structure:
>

>ccode town usegrp year period NumSales VolSales MedSales
>01    BROO  CND   1990 01     1        10000    0
>

>Period can have 17 different values (months from 01-12, quaters Q1-Q4 and YR)
>
>Now I want to 'reverse' the metric, e.g. I want to have data of 5 last years (period='YR'), e.g.:
>ccode town usegrp MedSalesYr-5 MedSalesYr-4... MedSalesYr
>
>I have two possible solutions in mind:
>1) 5 self-joins of the same table with different where expressions
>2) Create the empty table of desired structure, add a filtered index in the stats table (ccode+town+usegrp+cYear) for period='YR' and do seek and scan while or locate while.
>
> Do you have other ideas and what would be faster, what do you think?
>
> The stats table contains data for the whole state (RI,CT,MA) 3 usegrp (CND,1FA,ALL).
>
> Thanks in advance.

Ok, once again I answer to myself, since nobody answers this question. I will go ahead and implement the idea two. It gives me more control, progress bar, etc. And I believe, it would be fast enough.