This wretched SQL has me stumped!

[Edward]

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]

Edward,

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

Shervin
"Edward" <te********@hotmail.com> wrote in message
news:25*************************@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

[John Bell]

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" <te********@hotmail.com> wrote in message
news:25*************************@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

[Edward]

"Shervin Shapourian" <Sh**********@hotmail.com> wrote in message news:<vo************@corp.supernews.com>...

Edward,

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

Shervin
"Edward" <te********@hotmail.com> wrote in message
news:25*************************@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