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

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]