Improve company productivity with a Business Account.Sign Up

x
?
Solved

Need help with summarizing results between 2 related tables

Posted on 2013-12-06
7
Medium Priority
?
265 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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 Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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.

Join & Write a Comment

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

588 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