Hi All,

I have a query that has me stomped, to be exact it's the HAVING clause :-)

Here is the query:

SELECT
ZIPCODE_ANALYSIS.ACTIVITY_NAME AS ActivityName,
ZIPCODE_ANALYSIS.ZIP AS CustomerZip,
COUNT(ZIPCODE_ANALYSIS.C_ID) AS CountCID
FROM ZIPCODE_ANALYSIS
GROUP BY
ZIPCODE_ANALYSIS.ACTIVITY_NAME,
ZIPCODE_ANALYSIS.ZIP
HAVING
(ZIPCODE_ANALYSIS.ACTIVITY_DATE BETWEEN '02/01/2004' AND '02/29/2004')
ORDER BY 1

When this exact query runs, it returns ~3000 records.
The suspect record that I have a problem with looks like this:

ActivityName, CustomerZip, CountCID
-----------------------------------
Appointments, , 580

The 580 appointments does not look right in the month of February, and upon a
little investigation I found out that there are exactly 580 appointments records
returned by view ZIPCODE_ANALYSIS (but it contains 5 months of data).

If I run this query:
select * from ZIPCODE_ANALYSIS Z where z.ACTIVITY_NAME = 'Appointments' and
z.ZIP = '' and z.ACTIVITY_DATE BETWEEN '02/01/2004' AND '02/29/2004'

I get 211 records which seems to be right.

Well, I started playing with dates in the first query and here are my results:
BETWEEN '02/01/2004' AND '02/01/2004' = Didn't return that record
BETWEEN '02/01/2004' AND '02/13/2004' = Didn't return that record
BETWEEN '02/01/2004' AND '02/20/2004' = Didn't return that record
BETWEEN '02/01/2004' AND '02/25/2004' = Didn't return that record
BETWEEN '02/01/2004' AND '02/26/2004' = CountCID is 580
BETWEEN '02/01/2004' AND '02/27/2004' = CountCID is 580
BETWEEN '02/01/2004' AND '02/29/2004' = CountCID is 580

Can anybody explain to me what the hell is going on?

Btw, I am using IB 6 and haven't fired up FB yet, but I assume that the results
will be the same.

TIA

raf