Edward,

Use COUNT(DISTINCT tblResponses.fldQueryID) instead of COUNT(*)

Shervin


"Edward" <teddysnips@hotmail.com> wrote in message
news:25080b60.0310080640.2033c6b@posting.google.co m...
> 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