> Will this work ?
> H.Yr ||H.mo > '0801' and H.Yr||H.mo < '0702'

H.Mo || H.Yr I guess :)

But I don't know (one should try) if it does 0801 or 81?

And, any indices on the columns will not be used. Then again,
if only the month or the year is in the column, the index selectivity
isn't very high either - so a single column really is better.

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

> instead of the all excluding :
> (H.Yr >= 2001 and H.mo >=8) and (H.Yr <= 2002 and H.mo <=7))
>
>
> amrita .
>
> -----Original Message-----
> From: Martijn Tonies [mailto:m.tonies@...]
> Sent: Thursday, August 22, 2002 10:57 AM
> To: ib-support@yahoogroups.com
> Subject: Re: [ib-support] Calling all SQL Gurus...
>
>
> Hi,
>
> > just a thought, but can H.SALESAMT have NULL values?
> >
> > Have you tried to run the query without:
> >
> > HAVING
> > SUM ( H.SALESAMT ) <> 0
> >
> > I'm not sure, but SUM (H.SALESAMT) might return NULL if
> > one of the H.SALESAMT values is NULL.
>
> Event if it was - there's a difference between a (or more) records
> with NULL compared to no records at all :)
>
> Martijn Tonies
> InterBase Workbench - the developer tool for InterBase and Firebird
> http://www.interbaseworkbench.com <http://www.interbaseworkbench.com>
>
> Upscene Productions
> http://www.upscene.com <http://www.upscene.com>
>
> "This is an object-oriented system.
> If we change anything, the users object."
>
> > Regards,
> > Thomas Steinmaurer
> > IB LogManager 2.0 - The Logging/Auditing Tool for InterBase and Firebird
> > http://www.iblogmanager.com <http://www.iblogmanager.com>
> >
> > > -----Original Message-----
> > > From: Kevin Stanton [mailto:Kevin.Stanton@...]
> > > Sent: Thursday, August 22, 2002 5:37 PM
> > > To: IB-Support
> > > Subject: [ib-support] Calling all SQL Gurus...
> > >
> > >
> > > 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:
> > >
> > > 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?
> > >
> > > Thanks in advance for all ideas!
> > >
> > > Kevin
> > >
> > >
> > >
> > > 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/ <http://docs.yahoo.com/info/terms/>
> > >
> > >
> > >
> > >
> >
> >
> >
> > 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/

> <http://docs.yahoo.com/info/terms/>
> >
> >
> >
>
>
> 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
> <http://docs.yahoo.com/info/terms/> .
>
>
>
>
> [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/
>
>
>