Need help with summarizing results between 2 related tables

Hi Experts!

I have two tables and am trying to construct a tSQL query that returns some summary information.

The two tables are:
Conversations (ConversationID int PK, PersonName varchar, CompanyID int)
and
Messages (MessageID int PK, MessageText varchar, MessageTime datetime, MessageStatus varchar, ConversationID int FK)

Here are some example records:
Conversations Table:
ConversationID	PersonName	CompanyID
1		Steve		1
2		Sally		2
3		Dave		1
4		Debbie		1
5		Jim		2

Open in new window

Messages Table:
MessageID	MessageText	MessageTime		MessageStatus	ConversationID
1		Example Text 1	12/6/13 01:12:00	status 1	1
2		Example Text 2	12/6/13	01:13:00	status 2	1
3		Example Text 3	12/6/13	01:14:00	status 3	1
4		Example Text 4	12/6/13	01:29:00	status 1	2
5		Example Text 5	12/6/13 01:44:00	status 1	3
6		Example Text 6	12/6/13 01:45:00	status 2	3
7		Example Text 7	12/6/13 01:45:00	status 1	4
8		Example Text 8	12/6/13	01:46:00	status 1	5
9		Example Text 9	12/6/13	01:46:00	status 2	1

Open in new window


I would like to return summary information about the Conversations for a specific CompanyID ordered by the most recent MessageTime for the Messages in each Conversation. For example the results would look like the following when displaying for CompanyID = 1:
ConversationID	PersonName	LastMessageTime		MessageText	MessageStatus	MessageCount
1		Steve		12/6/13	01:46:00	Example Text 9	status 2	4
4		Debbie		12/6/13 01:45:00	Example Text 7	status 1	1
3		Dave		12/6/13 01:45:00	Example Text 6	status 2	2

Open in new window


Your help with constructing the tSQL to accomplish this is greatly appreciated!
DrevoAsked:
Who is Participating?
 
Brian CroweDatabase AdministratorCommented:
WITH cteMessage
AS
(
   SELECT ConversationID, MessageID, MessageText, MessageTime, MessageStatus,
      ROW_NUMBER() OVER(PARTITION BY ConversationID ORDER BY MessageTime DESC) AS RowNumber,
      COUNT() OVER(PARTITION BY ConversationID) AS MessageCount
   FROM Messages
)
SELECT C.ConversationID,
   C.PersonName,
   M.MessageTime AS LastMessageTime,
   M.MessageText,
   M.MessageStatus,
   M.MessageCount
FROM Conversations AS C
INNER JOIN cteMessage AS M
   ON C.ConversationID = M.ConversationID
   AND M.RowNumber = 1
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, give this a whirl..
SELECT c.ConversationID, c.PersonName, Max(m.MessageTime) as LastMessageTime, m.MessageText, m.MessageStatus
FROM Conversation c
   JOIN Messages m ON c.ConversationID = m.ConverstionID
GROUP BY  c.ConversationID, c.PersonName, m.MessageText, m.MessageStatus
ORDER BY Max(m.MessageTime)

Open in new window

Also I have an article on SQL Server GROUP BY solutions that might be a good read.
0
 
Brian CroweDatabase AdministratorCommented:
WITH cteMessage
AS
(
   SELECT ConversationID, MessageID, MessageText, MessageTime, MessageStatus,
      ROW_NUMBER() OVER(PARTITION BY ConversationID ORDER BY MessageTime DESC) AS RowNumber
   FROM Messages
)
SELECT C.ConversationID, C.PersonName,
   M.MessageTime AS LastMessageTime,
   M.MessageText, M.MessageStatus
FROM Conversations AS C
INNER JOIN cteMessage AS M
   ON C.ConversationID = M.ConversationID
   AND M.RowNumber = 1
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DrevoAuthor Commented:
Thanks for you help with this! I really appreciate it.

BriCrowe's solution works, though it's missing the MessageCount. Can you help with getting it to provide this?

Jimhorn's solution ends up returning all of the Messages and not just the most recent for the related Conversation record. It also lacks a count of the messages related to the Conversation record.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Yep, didn't scroll all the way to the right.
SELECT 
   c.ConversationID, 
   c.PersonName, 
   Max(m.MessageTime) as LastMessageTime, 
   m.MessageText, 
   m.MessageStatus, 
   Count(m.MessageID) as MessageCount
FROM Conversation c
   JOIN Messages m ON c.ConversationID = m.ConverstionID
GROUP BY  c.ConversationID, c.PersonName, m.MessageText, m.MessageStatus, 
ORDER BY Max(m.MessageTime) 

Open in new window

0
 
Brian CroweDatabase AdministratorCommented:
I had a typo in my last post:

...
COUNT(1) OVER(PARTITION BY ConversationID) AS MessageCount
...

I left the parentheses empty on the COUNT function
0
 
DrevoAuthor Commented:
Thanks! BriCrowe's solution now works. I added "ORDER BY LastMessageTime DESC" and "WHERE C.CompanyID = 1" to finish it off.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.