Solved

Need help with summarizing results between 2 related tables

Posted on 2013-12-06
7
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

617 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