Hi Martijn,
Yes, I've been thinking about changing the table.

What I'm trying to do is get all the months >= than 8 for the year 2001 and
all the months <= 7 for the year 2002. I thought with the proper
parenthesis in place I could achieve this.

I think I will modify the table like you said and just create a true date
field - either first or last day of the month would work as I'm really only
interested in the month/year.

Also, H.SalesAmt is verified on a before insert trigger to make sure the
amount is at least zero.

Thanks for the replies.

Kevin

-----Original Message-----
From: Martijn Tonies [mailto:m.tonies@...]
Sent: Thursday, August 22, 2002 8:51 AM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Calling all SQL Gurus...


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


To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]