May 20, 2011 at 3:08 pm
I have two tables: vcases and comments. What I am trying to find is the latest/newest comments.create_date based on a specific vcases.creator. When I use the max function in the query below, it is returning every max comment per comments.create_user_id and sometimes two. I only want it to show the latest comment per vcases.case_number. Any help is appreciated.
Here is the query:
SELECT DISTINCTvcases.case_number AS 'Case', vcases.style AS 'Client', MAX(comments.create_date), AS 'Date', comments.create_user_id AS 'User'
FROMvcases INNER JOIN comments ON vcases.case_sk = comments.case_sk
WHERE(vcases.creator = 'Doe, John') AND (DATEDIFF(DAY,comments.create_date,getdate()) > 30)
GROUP BYcomments.create_date, vcases.case_number, vcases.style, comments.create_user_id
--HAVINGDATEDIFF(DAY,comments.create_date,getdate()) > 30
ORDER BY'Client', 'Date' ASC
And this is the result:
Case Client Date User
---------------------------------------------------------------------------
123456789 Doe, John 2010-11-12 14:27:48.737 USER1
123456789 Doe, John 2010-11-15 10:44:15.577 USER2
123456789 Doe, John 2010-11-15 14:56:58.950 USER3
123456789 Doe, John 2010-11-22 16:37:06.400 USER3
123456789 Doe, John 2010-11-23 11:14:28.800 USER4
123456789 Doe, John 2010-12-09 15:19:47.967 USER5
123456789 Doe, John 2010-12-28 10:49:40.417 USER5
123456789 Doe, John 2011-01-05 09:58:55.417 USER6
123456789 Doe, John 2011-01-05 16:30:02.007 USER7
123456789 Doe, John 2011-01-12 16:20:14.060 USER8
123456789 Doe, John 2011-01-12 16:27:44.107 USER8
The only row that I'd like to retrieve is: 123456789 Doe, John 2011-01-12 16:27:44.107 USER8
May 20, 2011 at 3:12 pm
Your group by is causing that. Remove comments.create_date from the group by.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2011 at 3:22 pm
SQLRNNR (5/20/2011)
Your group by is causing that. Remove comments.create_date from the group by.
I tried that and it yielded these results:
Case Client Date User
---------------------------------------------------------------------------
123456789Doe, John2010-11-12 14:27:48.737USER1
123456789Doe, John2010-11-15 10:44:15.577USER2
123456789Doe, John2011-01-12 16:20:14.060USER3
123456789Doe, John2011-01-12 16:27:44.107USER4
It only removed the duplicate USER3 rows. Why isn't it just retrieving one row?
May 20, 2011 at 3:24 pm
If you only want one record period and not one record per user that created a comment, then you need to remove more items from your group by
, comments.create_user_id
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2011 at 4:10 pm
Maybe the problem is with the GROUP BY itself. There are a couple of approaches you can try instead. Which is more efficient may depend on indices you have in place.
Approach 1: CTE with Row_Number()
;WITH Case_Comments AS (
SELECT DISTINCT
vcases.case_number AS [Case]
, vcases.style AS [Client]
, comments.create_date AS [Date]
, comments.create_user_id AS [User]
, Row_Number() OVER( PARTITION BY vcases.case_number ORDER BY comments.create_date DESC ) AS rn
FROM vcases
INNER JOIN comments
ON vcases.case_sk = comments.case_sk
WHERE vcases.creator = 'Doe, John'
AND DATEDIFF(DAY,comments.create_date,getdate()) > 30
)
SELECT [Case], [Client], [Date], [User]
FROM Case_Comments
WHERE rn = 1
ORDER BY 'Client', 'Date' ASC
This approach will work even if you have multiple comments with the exact same date. The other approaches will work if you can guarantee some unique field in the comments.
Approach 2:
SELECT DISTINCT
vcases.case_number AS [Case]
, vcases.style AS [Client]
, comments.create_date AS [Date]
, comments.create_user_id AS [User]
FROM vcases
INNER JOIN comments
ON vcases.case_sk = comments.case_sk
CROSS APPLY (
SELECT TOP 1 c2.YourUniqueField
FROM comments AS c2
WHERE c2.case_sk = vcases.case_sk
ORDER BY c2.create_date DESC
) AS LastComment
WHERE vcases.creator = 'Doe, John'
AND DATEDIFF(DAY,comments.create_date,getdate()) > 30
AND comments.YourUniqueField = c2.YourUniqueField
If you can't guarantee a unique field in your comments table, the following will get close if the date is unlikely to be EXACTLY the same down to thousandths of a millisecond.
SELECT DISTINCT
vcases.case_number AS [Case]
, vcases.style AS [Client]
, comments.create_date AS [Date]
, comments.create_user_id AS [User]
FROM vcases
INNER JOIN comments
ON vcases.case_sk = comments.case_sk
CROSS APPLY (
SELECT TOP 1 Max(c2.create_date) as LastCommentDate
FROM comments AS c2
WHERE c2.case_sk = vcases.case_sk
HAVING Max(c2.create_date) = comments.create_date
ORDER BY c2.create_date DESC
) AS LastComment
WHERE vcases.creator = 'Doe, John'
AND DATEDIFF(DAY,comments.create_date,getdate()) > 30
Of course, all of this code is untested, because you haven't supplied easily consumable sample data. If you want tested code, check out this article http://www.sqlservercentral.com/articles/Best+Practices/61537/
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 24, 2011 at 9:16 am
Hi this is another option you can use to solve your issue; I don’t have a SQL server to test the code I hope it works
SELECT
vcase.case_number AS 'Case',
vcase.style AS 'Client',
com.create_date AS 'Date',
com.create_user_id AS 'User'
FROM vcases vcase
INNER JOIN comments com ON vcase.case_sk = com.case_sk
WHERE (vcases.creator = 'Doe, John') AND (DATEDIFF(DAY,com.create_date,getdate()) > 30)
AND (com.create_date=(SELECT MAX(create_date) FROM comments WHERE case_sk= com.case_sk))
GROUP BY com.create_date, vcase.case_number, vcase.style, com.create_user_id
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply