"Shervin Shapourian" <ShShapourian@hotmail.com> wrote in message news:<vo8gshad7mlvab@corp.supernews.com>...
> 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

This one worked! Note, however, that because fldQueryID is a SQL
Server Uniqueidentifier field it had to be cast thus:

SELECT COUNT( DISTINCT (CONVERT(varchar(38),
tblResponses.fldQueryID))) AS NumResponses

Many, many thanks to the other people who took the trouble to respond.

Edward