SCENARIO:

SQL Server 2000 Back End.

The customer calls in with a problem and a QUERY is raised. The date
of creation of the record is in the fldDateQuery column.

The user responds to the customer and a RESPONSE is raised. It
contains the ID of the associated QUERY and the date of creation of
the RESPONSE record is in the fldDateResponse column.

The user wishes to know how many queries in the last month were
responded to on the same day.

CANDIDATE SQL:

SELECT COUNT(*) AS NumResponses
FROM tblQuery
INNER JOIN tblResponses ON tblQuery.fldQueryID =
tblResponses.fldQueryID
WHERE (tblQuery.fldDateQuery < CONVERT(DATETIME,'2003-9-8 00:00:00',
102))
AND (tblQuery.fldDateQuery > CONVERT(DATETIME,'2003-8-8 00:00:00',
102))
AND (DATEDIFF(Day, tblQuery.fldDateQuery,
tblResponses.fldDateResponse) <= 1)

PROBLEM:

If a query has more than one response raised on it within a day of the
query being logged, it counts all those responses. In other words,
the SQL counts the number of matching RESPONSES, and not the number of
QUERIES.

TIA

Edward