SEO

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

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

Hi

I can't check this out, but try counting fldDateResponse formatted to a day
(i.e. use CONVERT).

e.g COUNT( CONVERT(char(8),fldDateResponse,112) )

John

"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

"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