Hi Kevin,

> I have a history table with two fields: yr & mo (year and month) defined

as

> small ints.
>
> Possibly have a design problem here.
>
> When running a report, a user wants to select the date range from 8/1/01
> thru 7/31/2002.
>
> I've been trying various Selects but can't quite get it.
>
> The below does not select anything:

Well that makes sense... (see below)

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

How can a H.Mo be >=8 and <=7 at the same time? Or at least, that's what
I think is happening... Reading all your parenthesis makes be do this:

and ( ( A and B ) and ( C and D ) )

which, I believe, can be written as:

and A and B and C and D

And there you have it - (B and D) will never return True.


ok - multiple possibilities here...

One thing could be to adjust the table in such a way that it stores
full dates - this way you could simply say:

H.DateField > '2001/08/01' and H.DateField < '2002/07/01'

That sure is the option I would go for.

Can you change the table or is this a no-go?

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