> >
> > The above works fine and returns in seconds on huge volumes of data, but
> > some stores from the FEB period did not exist in the JAN period, so they
> > don't appear in the data.
> > I would have thought the following left outer join would solve the problem
> > ...
> >
> > select b.name, b.region, b.orderappend, a.gp gpa, a.turnover turnovera,
> > a.orders ordersa, a.rank ranka, b.gp gpb, b.turnover turnoverb, b.orders
> > ordersb, b.rank rankb from
> > P_GROUP_GP_RANKED('01-feb-2004','14-feb-2004') b left outer join
> > P_GROUP_GP_RANKED('01-jan-2004','14-jan-2004') a on a.orderappend =
> > b.orderappend
> >
> > ... and it does, but only after bogging the server down for up to 15
> >minutes

Having had to do something very similar for a chain store here, my
suggestion is that you have an ACCOUNTINGPERIOD table (where the user can
define the accounting periods) and in your (b) table, have an
ACCOUNTINGPERIOD field (INTEGER).

When your application captures whatever data it is processing, assign a
ACCOUNTINGPERIOD (which would be a value between 1 and 12). Be aware that
some transactions (like provisions for expenditure, for example) will allow
for transactions to occur in accounting periods in the future ...

That makes sorting the data by accounting period real easy, and also makes
sure that each transaction is assigned to the correct accounting period.

HTH

Tim


[Non-text portions of this message have been removed]