Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need help with summarizing results between 2 related tables

Posted on 2013-12-06
7
Medium Priority
?
260 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 66

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 34

Accepted Solution

by:
Brian Crowe earned 2000 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 66

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

876 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