--- In firebird-support@yahoogroups.com, "Leyne, Sean" <Sean@...>
wrote:

>
> 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
>

Thanks Sean,

TheFilterId is used to filter the products, customers can make
decision how many products they want to analyze. When they choose
several products I will create a new filter id, and insert all the
products primary key to the filter table.

best regards,
Roc