By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,122 Members | 906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,122 IT Pros & Developers. It's quick & easy.

This wretched SQL has me stumped!

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

P: n/a
Try this sql in Sql Server Query Analyzer:

Select Count(t3.Queries) From
(
SELECT COUNT(t2.fldQueryID) AS Queries
FROM tblQuery t1
INNER JOIN tblResponses t2 ON t1.fldQueryID =
t2.fldQueryID
WHERE (t1.fldDateQuery Between '8/8/03' And '9/8/03')
AND (DATEDIFF(Day, t1.fldDateQuery,
t2.fldDateResponse) <= 1)
Group By t2.fldQueryID Having Count(t2.fldQueryID)>1)
) t3

This should give you the count of just the queries and not the count of
responses. Like say query1 has 3 responses for a day that would be a
count of 1

Count(t2.fldQueryID)>1

Query 2 has only one response for a day, no grouping - wont show up,
query3 has 2 responses for a day, that would be a count of 1. So Query1
and Query3 are counted. That would be a count of 2 queries. Note: I
only set this up for one day. Haven't tried it for multiple day. Just
to give you some ideas. Oh yeah, I think it would be simpler if you
used the Between statement instead of Convert, well based on what I saw
in your original query, looks like Between would work (maybe other
criteria to consider, if not - try between).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #4

P: n/a
"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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.