Hi Kevin,

you can also take a CHAR field or, for example, a simple INTEGER field -
and fill that with a trigger instead of having a Day value that's giving
troubles...

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

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