> Am I too late? This can be done with a very small change to your code:
>
> SELECT
> H.CUSTNO, H.CUSTNAME,
> SUM ( H.SALESAMT ) AS SALESAMT, SUM ( H.GROSSMARGIN ) AS GROSSMARGIN
> FROM CUST_HIST H
> WHERE
> H.COMPANY = '01' AND DIVISION = '01'
> and ((H.Yr = 2001 and H.mo >=8) or (H.Yr = 2002 and H.mo <=7))

lol - should have seen this one too ... then again, as you're saying, it
only
works if the years are this close...

Martijn Tonies
InterBase Workbench - the developer tool for InterBase and Firebird
http://www.interbaseworkbench.com

Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

> GROUP BY
> H.CUSTNO, H.CUSTNAME
> HAVING
> SUM ( H.SALESAMT ) <> 0
> ORDER BY
> 3 DESC
>
> If you use parameters and are uncertain whether lowest and highest year is
> successive, you will have to add a little bit (just showing the actual

part

> of the where clause):
>
> and ((H.Yr = :YrMin and H.mo >= :MoMin) or (H.Yr BETWEEN :YrMin+1 AND
> :YrMax-1) or (H.Yr = :YrMax and H.mo <=:MoMax))
>
> If there is a chance of :YrMin and :YrMax being the same, it gets a bit
> more complicated:
>
> and ((H.Yr = :YrMin and H.mo >= :MoMin and not (H.Yr = :YrMax)) or (H.Yr
> BETWEEN :YrMin+1 AND :YrMax-1) or (H.Yr = :YrMax and H.mo <=:MoMax and not
> (H.Yr = :YrMin)) or (H.Yr = :YrMin and H.Yr = :YrMax and H.mo BETWEEN
> :MoMin and :MoMax))
>
> There is no point in having a separate index for "mo" (too low
> selectivity), but a compound index on (Yr, Mo) could possibly be useful
> (and then if there are many entries every year add the PK at the end).
> However, as you can see from the above line, things might have been a bit
> simpler if you'd used a date column rather than separate columns for year
> and month.
>
> HTH,
> Set
> -when complicating simple things, we really appear to be gurus...
>
> At 08:37 22.08.2002 -0700, you wrote:
> >The below does not select anything:
> >
> >SELECT
> > H.CUSTNO, H.CUSTNAME,
> > SUM ( H.SALESAMT ) AS SALESAMT, SUM ( H.GROSSMARGIN ) AS GROSSMARGIN
> >FROM CUST_HIST H
> >WHERE
> > H.COMPANY = '01' AND DIVISION = '01'
> >and ((H.Yr >= 2001 and H.mo >=8) and (H.Yr <= 2002 and H.mo <=7))
> >GROUP BY
> > H.CUSTNO, H.CUSTNAME
> >HAVING
> > SUM ( H.SALESAMT ) <> 0
> >ORDER BY
> > 3 DESC
> >
> >Is there a way to get this to work or do a need to create a field YYYYMM

and

> >append the two fields together?
>
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>