Martijn,

Correct, but it made a simple query :-)

By looks he wants a years worth of data, my query will require the server to
serve 2 years worth of data to the query engine instead of the exact period.

I'm sure his DB would be better with a date field in it rather than two
fields or even a yyyymm integer field.

Have a good weekend.

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




""Kevin Stanton"" <Kevin.Stanton@...> wrote in message
news:NBBBLCJLMKMLEHCAFCCOEEEONKAA.Kevin.Stanton@......

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