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.

 If it's not broken, fix it until it is.


My Blog