On 5 Nov 2002 at 18:07, c_pradelli@... wrote:

>The table items have about 5 millions of records,
>that's why I looking for a
>fast method to do the query.

Right indices make query near independed from number of records.
You also can try to denormalize your data moving sell-date into
items. It would remove one table from SELECT and increase selectivity
of the code-date index.

>------------------------------------------------------------
>Does anybody knows how to simplify this query, or to
>avoid the use of
>temporary tables?
>any idea???

Try this:

SELECT p.Code,
(SELECT sum(i.amount) FROM items i JOIN header h
ON i.billnumber=h.billnumber
WHERE i.Code=p.Code AND h.date BETWEEN dateA1 AND dateA2)
AS Sales1,
/* I believe that every item has appropriate header */
(SELECT sum(i.amount) FROM items i JOIN header h
ON i.billnumber=h.billnumber
WHERE i.Code=p.Code AND h.date BETWEEN dateB1 AND dateB2)
AS Sales2
FROM products p
/* I also believe that you want all products codes in result record
set even if this product wasn's sold in both periods */

Or you can try FULL JOIN of your SP_TEMPDATAs if you don't need to
see non-sold product codes.

SY, Dimitry Sibiryakov.