Wow, I'm gone for an afternoon and come back to a flood of great ideas -
thanks!
I will be trying them right away!
Kevin

-----Original Message-----
From: Martijn Tonies [mailto:m.tonies@...]
Sent: Friday, August 23, 2002 2:28 AM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Calling all SQL Gurus...


Hi,

> Martijn & Kevin,
>
> How about:
>
> H.Yr between 2001 and 2002 /*to use indices */

The problem with this index (if you only have indexed H.YR) is that it's
selectivity is way low -> values most probably ranging from 199x to 200x?

I would at least create a compound index on columns Yr and Mo to increase
selectivity...

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

> and ((H.Yr*100+H.mo) between 200108 and 200207) /* to narrow it down
> further*/
>
> yr must be a positive integer.
> mo must be a positive integer < 100.
>
> This way you get use of indices on yr if available. Less fuss than a

UDF.

>
> HIH
>
> Jason Chapman
> JAC2 Consultancy
>
> Training - Development - Consultancy
> Delphi, InterBase, Firebird, OOAD, Development lifecycle assistance,
> Troubleshooting projects, QA.....
> www: When I get round to it....
> Mob: (+44) 07966 211 959 (preferred)
> Tel: (+44) 01928 751088
>
>
>
>
> ""Martijn Tonies"" <m.tonies@...> wrote in message
> news:000501c249f3$b56dd5b0$0a02a8c0@seal...
> > 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

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/

>
>

Yahoo! Groups Sponsor
ADVERTISEMENT



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]