December 14, 2004 at 4:37 pm
I need help with the query below. The OpenedTot column has a value of 1 for each row which is not correct. It should be a total count. I thought the Correlated SubQuery would have done the trick but it still returns a value of 1 for each row for the OpenedTot Column. Can someone tell me where I'm going wrong here?
DECLARE @StartDate DATETIME, @EndDate DATETIME
SET @EndDate = (SELECT MAX(OpenedDateTime)
FROM vwComplaintDetails)
SET @StartDate = DATEADD(Day, -1, @EndDate)
SELECT A.UserID, A.CategoryDescript, A.ComplaintType, A.OpenedDateTime, COUNT(A.ComplaintID) AS OpenedTot
FROM vwComplaintDetails A
WHERE A.OpenedDateTime >= @StartDate AND A.OpenedDateTime <= @EndDate
AND A.ComplaintType = 'C'
AND A.OpenedDateTime = (SELECT MAX(OpenedDateTime)
FROM vwComplaintDetails B
WHERE A.OpenedDateTime = B.OpenedDateTime)
GROUP BY A.UserID, A.CategoryDescript, A.ComplaintType, A.OpenedDateTime
December 14, 2004 at 5:07 pm
It maybe because you group by A.OpenedDateTime and it has to be just date without time part
should be something like
SELECT A.UserID, A.CategoryDescript, A.ComplaintType, convert(varchar(10),A.OpenedDateTime,101), COUNT(A.ComplaintID) AS OpenedTot
FROM vwComplaintDetails A
WHERE A.OpenedDateTime >= @StartDate AND A.OpenedDateTime <= @EndDate
AND A.ComplaintType = 'C'
AND A.OpenedDateTime = (SELECT MAX(OpenedDateTime)
FROM vwComplaintDetails B
WHERE A.OpenedDateTime = B.OpenedDateTime)
GROUP BY A.UserID, A.CategoryDescript, A.ComplaintType, convert(varchar(10),A.OpenedDateTime,101)
December 14, 2004 at 5:11 pm
this part of your query I do not understand, it does not look that you need it at all
AND A.OpenedDateTime = (SELECT MAX(OpenedDateTime)
December 14, 2004 at 5:29 pm
Herb,
That worked. I still don't understand why we have to omit the time part of the query but it worked.
If I don't perform the Correlated SubQuery, the OpenedTot Column returns a value of 1 which is not correct. So I figured that I have to get the max OpenedDateTime to be able to get the OpenedDateTime Column to do the Count properley. It's difficult to explain to you without getting you to see the data that is returned, and I assure you will understand once you see the data.
Do you want me to post a couple of rows so that you can what I'm talking about?
December 14, 2004 at 7:35 pm
That how aggregate function works with Group by. It aggregates all records based on your grouping. Based on your query you want to count events for the entire day and not for a single moment. That is why you got 1 for every row
December 14, 2004 at 8:46 pm
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply