Solved

Need help with summarizing results between 2 related tables

Posted on 2013-12-06
7
251 Views
Last Modified: 2013-12-06
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!
0
Comment
Question by:Drevo
  • 3
  • 2
  • 2
7 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39702198
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39702209
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
 

Author Comment

by:Drevo
ID: 39702348
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 34

Accepted Solution

by:
Brian Crowe earned 500 total points
ID: 39702373
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39702379
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
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39702386
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
 

Author Comment

by:Drevo
ID: 39702489
Thanks! BriCrowe's solution now works. I added "ORDER BY LastMessageTime DESC" and "WHERE C.CompanyID = 1" to finish it off.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question