Roc,

> So many nice guys here,
> The SQL is very simple:
>
> SELECT s.PLU, s.DESCRIPTION,
> CAST( 'N' AS VARCHAR(1) ) AS ISLINKED,
> CAST( '-1' AS VARCHAR(10) ) AS STOREID,
> SUM(A.QUANTITYSOLD*s.PACKQTY) AS QUANTITY,
> SUM(B.QUANTITYSOLD*s.PACKQTY) AS PROMQUANTITY,
> SUM(D.QUANTITYSOLD*s.PACKQTY) AS PURCHQUANTITY,
> SUM(A.SALESEXGST) AS SALES,
> SUM(A.SALESEXGST-A.COSTEXGST) AS SALESPROFIT,
> SUM(B.SALESEXGST) AS PROMSALES,
> SUM(B.SALESEXGST-B.COSTEXGST) AS PROMPROFIT,
> SUM(D.COSTEXGST) AS PURCHSALES
> FROM PLUWEEK A
> JOIN STDPROD s ON (s.PLU = A.PLU )
> and( A.SALESDATE BETWEEN :STARTDATE AND :ENDDATE)
> and(A.STOREID=:THESTOREID)
> JOIN FILTERSTD F
> ON( S.PLU= F.CODE1 )
> AND( F.FILTERID = :THEFILTERID )
> LEFT OUTER JOIN PRMPLUWEEK b ON (A.STOREID=B.STOREID) AND
> (A.SALESDATE=B.SALESDATE) AND (A.PLU=B.PLU)
> LEFT OUTER JOIN PURPLUWEEK D ON (A.STOREID=d.STOREID) AND
> (A.SALESDATE=d.SALESDATE) AND (A.PLU=D.PLU)
> group by S.PLU, S.DESCRIPTION

What does the TheFilterID value represent?

I would create a compound index on StoreID + SalesDate -- this would
narrow the range of your processing.


Sean