Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Grouping emails by conversation thread

Author  Topic 

bpsintl
Posting Yak Master

132 Posts

Posted - 2013-01-25 : 03:44:24
Hi, I have 2 tabled, inbox and sent_items, both with similar columns.


INBOX
id, date_received, from_email, subject, threadid

SENT_EMAILS
id, date_sent, to_email, subject, threadid

There are more columns but those are the ones I have a question about. When an email is replied to from the system, in the background the system puts in the same threadid in the sent_emails as the email being replied to's threadid. And, when a new email comes in if it is a reply to one sent out via the system, the same thrteadid is added to that new email in the inbox table.

The thread id is a unique identifier column type.

My question is that how can I display the emails in the inbox and group them by threadid the same way as gmail does?

So in the inbox I would want to show the subject and date of the most recent email in the "conversation" but also a count to show how many emails are in that conversation.

The tricky bit is that I need to include the emails in the sent_emails table in the count total.

I hope that makes sense......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 03:56:00
[code]
SELECT threadid,subject,date_received,t.Cnt + COALESCE(s.Cnt,0) AS emailcount
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY threadid ORDER BY date_received DESC) AS Seq,
COUNT(1) OVER (PARTITION BY threadid) AS Cnt
FROM INBOX
)t
OUTER APPLY (SELECT COUNT(1) AS Cnt
FROM Sent_Emails
WHERE threadid=threadid
)s
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bpsintl
Posting Yak Master

132 Posts

Posted - 2013-01-25 : 05:30:58
Hi, thanks for your reply but that does not seem to work. It only ever shows one email in the inbox (the most recent one).

What it needs to do is display the most recent email in the thread (if there is a thread). Not all emails in the inbox are part of a thread.

I will try and explain it better below:

Lets say there are 0 emails in the inbox and 0 in the sent items. I send an email to someone and that email is put in the sent items table. The recipient of that email replies to it and the reply comes into the inbox table, but the threadid of the two emails are the same.

In the mean time another new emails comes into the inbox so that also needs to show, but at this moment in time it is not in a thread.

So the inbox would show 2 emails ordered by date_received desc. One of the emails would have a count of 2 because there are 2 emails in that conversation and the other would have a count of 0 because it isn't in a conversation yet.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 05:37:50
can you put together sample data to illustrate your scenario?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bpsintl
Posting Yak Master

132 Posts

Posted - 2013-02-01 : 05:39:02
Hi, I have changed the table slightly. Now all emails come into the one table "emails". The "email_type" column designates if it was received or sent (0 = received, 1 = sent).

I basically want to display only emails that are email_type=0 but group them via the threadid column.

So the inbox should show by date_added desc but if there are multiple emails with the same threadid, this means that they are part of the same conversation so only the date_added, subject and from email should be listed but the most recent in the thread.

Here is an example extract:


emailid guid date_added to_email from_email subject threadid email_type
4893 CF9AE779-2C34-41B0-9B7E-E2592AE00EAA 04/02/2012 xxxx xxxxx Re: Regarding the trial request form you submitted CF9AE779-2C34-41B0-9B7E-E2592AE00EAA 0
4898 EC239259-E69F-481A-842E-58FC91B401AA 05/02/2012 xxxx xxxxx Re: Regarding the trial request form you submitted CF9AE779-2C34-41B0-9B7E-E2592AE00EAA 0
4899 541AE752-EF27-4370-9D78-D8B2446CBEC2 05/02/2012 xxxx xxxxx Re: Regarding the trial request form you submitted CF9AE779-2C34-41B0-9B7E-E2592AE00EAA 1
4917 F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 05/02/2012 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 0
5019 5DFBF5FF-62D9-41AB-9EDF-073B8AB8BF2B 07/02/2012 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 1
5056 15C170F4-40BF-48DD-9D58-E07CA963693D 08/02/2012 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 1
5059 BB1AAC3F-0019-4E5A-A53A-AB7979ED56E9 08/02/2012 xxxx xxxxx Re: Regarding the trial request form you submitted BB1AAC3F-0019-4E5A-A53A-AB7979ED56E9 0
5072 D375148B-C357-49F5-BDD4-C3844AD119B2 08/02/2012 xxxx xxxxx Re: Regarding the trial request form you submitted BB1AAC3F-0019-4E5A-A53A-AB7979ED56E9 1
5074 D674D2C0-5299-4A49-A3EC-4DD6F4B9BD5F 08/02/2012 xxxx xxxxx Re: Regarding the trial request form you submitted BB1AAC3F-0019-4E5A-A53A-AB7979ED56E9 0
5124 455D2C51-76A9-4D8F-9A4D-4938C272CED9 10/02/2012 xxxx xxxxx website query 455D2C51-76A9-4D8F-9A4D-4938C272CED9 0
5129 F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 10/02/2012 xxxx xxxxx Re: half term training F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 0
5136 99B1137C-CC4B-49D8-AC44-D21E8971A72C 10/02/2012 xxxx xxxxx Re: half term training F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 1
5140 F0144C14-350F-45B1-971B-435C834C1B9D 11/02/2012 xxxx xxxxx Re: CREDIT FOR CANCELLED SESSION F0144C14-350F-45B1-971B-435C834C1B9D 0
5141 DEE3C64B-FD04-4119-BC34-3659EF30F5AB 11/02/2012 xxxx xxxxx Re: CREDIT FOR CANCELLED SESSION F0144C14-350F-45B1-971B-435C834C1B9D 1
5144 6BCBEA9F-BD0F-4494-B14D-65A9391F32EB 11/02/2012 xxxx xxxxx RE: CREDIT FOR CANCELLED SESSION F0144C14-350F-45B1-971B-435C834C1B9D 1
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-01 : 06:35:28
what should be output from above sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bpsintl
Posting Yak Master

132 Posts

Posted - 2013-02-01 : 07:21:44
Ok, the following table:


emailid guid date_added to_email from_email subject threadid email_type
4893 CF9AE779-2C34-41B0-9B7E-E2592AE00EAA 04/02/2012 20:02 xxxx xxxxx Re: Regarding the trial request form you submitted CF9AE779-2C34-41B0-9B7E-E2592AE00EAA 0
4898 EC239259-E69F-481A-842E-58FC91B401AA 05/02/2012 11:31 xxxx xxxxx Re: Regarding the trial request form you submitted CF9AE779-2C34-41B0-9B7E-E2592AE00EAA 0
4899 541AE752-EF27-4370-9D78-D8B2446CBEC2 05/02/2012 11:33 xxxx xxxxx Re: Regarding the trial request form you submitted CF9AE779-2C34-41B0-9B7E-E2592AE00EAA 1
4917 F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 05/02/2012 15:42 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 0
5019 5DFBF5FF-62D9-41AB-9EDF-073B8AB8BF2B 07/02/2012 11:02 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 1
5056 15C170F4-40BF-48DD-9D58-E07CA963693D 08/02/2012 10:33 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 1
5059 BB1AAC3F-0019-4E5A-A53A-AB7979ED56E9 08/02/2012 13:12 xxxx xxxxx Re: Regarding the trial request form you submitted BB1AAC3F-0019-4E5A-A53A-AB7979ED56E9 0
5072 D375148B-C357-49F5-BDD4-C3844AD119B2 08/02/2012 22:32 xxxx xxxxx Re: Regarding the trial request form you submitted BB1AAC3F-0019-4E5A-A53A-AB7979ED56E9 1
5074 D674D2C0-5299-4A49-A3EC-4DD6F4B9BD5F 08/02/2012 22:52 xxxx xxxxx Re: Regarding the trial request form you submitted BB1AAC3F-0019-4E5A-A53A-AB7979ED56E9 0
5124 455D2C51-76A9-4D8F-9A4D-4938C272CED9 10/02/2012 15:12 xxxx xxxxx website query 455D2C51-76A9-4D8F-9A4D-4938C272CED9 0
5129 F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 10/02/2012 18:52 xxxx xxxxx Re: half term training F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 0
5136 99B1137C-CC4B-49D8-AC44-D21E8971A72C 10/02/2012 22:24 xxxx xxxxx Re: half term training F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 1
5140 F0144C14-350F-45B1-971B-435C834C1B9D 11/02/2012 10:12 xxxx xxxxx Re: CREDIT FOR CANCELLED SESSION F0144C14-350F-45B1-971B-435C834C1B9D 0
5141 DEE3C64B-FD04-4119-BC34-3659EF30F5AB 11/02/2012 10:16 xxxx xxxxx Re: CREDIT FOR CANCELLED SESSION F0144C14-350F-45B1-971B-435C834C1B9D 1
5144 6BCBEA9F-BD0F-4494-B14D-65A9391F32EB 11/02/2012 11:32 xxxx xxxxx RE: CREDIT FOR CANCELLED SESSION F0144C14-350F-45B1-971B-435C834C1B9D 1


Would be output like:


5140 F0144C14-350F-45B1-971B-435C834C1B9D 11/02/2012 10:12 xxxx xxxxx Re: CREDIT FOR CANCELLED SESSION F0144C14-350F-45B1-971B-435C834C1B9D 0
5129 F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 10/02/2012 18:52 xxxx xxxxx Re: half term training F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 0
5124 455D2C51-76A9-4D8F-9A4D-4938C272CED9 10/02/2012 15:12 xxxx xxxxx website query 455D2C51-76A9-4D8F-9A4D-4938C272CED9 0
5074 D674D2C0-5299-4A49-A3EC-4DD6F4B9BD5F 08/02/2012 22:52 xxxx xxxxx Re: Regarding the trial request form you submitted BB1AAC3F-0019-4E5A-A53A-AB7979ED56E9 0
4917 F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 05/02/2012 15:42 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 0
4898 EC239259-E69F-481A-842E-58FC91B401AA 05/02/2012 11:31 xxxx xxxxx Re: Regarding the trial request form you submitted CF9AE779-2C34-41B0-9B7E-E2592AE00EAA 0


We are only showing emails with an "email_type" of 0 and they are ordered by date_added desc. However, if more than one email with email_type=0 has a threadid that is the same, only the newest of those in that thread is shown.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-01 : 11:53:05
Thanks for supplying sample data, that really helps. But, it'd be even better if you went one step further and supplied it in a consumable format.

Here are some links to help you with that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-01 : 12:39:52
[code]
SELECT emailid,
guid,
date_added,
to_email,
from_email,
subject,
threadid,
email_type
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY threadid ORDER BY date_added DESC) AS Seq,*
FROM tablename
WHERE email_type =0
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bpsintl
Posting Yak Master

132 Posts

Posted - 2013-02-01 : 13:31:23
Great, thanks for that it seems to work a treat. However I forgot to mention I have this query :


SELECT * from (SELECT ROW_NUMBER() OVER(ORDER BY e.date_added desc) AS rownum, e.emailid, e.date_added, e.to_email, e.from_email, e.cc_emails, e.subject, e.attachments, e.threadid, e.saved_date,
e.read_date, e.read_by_userid, e.replied_date, e.forwarded_date, (select count(emailid) as c

from emails where clubid=3 and isdate(deleted_date)=0 and isdate(saved_date)=0 and email_type=0and isdate(archived_date)=0) as totrows, u.firstname + ' ' + u.lastname as name
FROM emails e LEFT JOIN users u ON e.userid = u.user_id

where e.clubid=3 and isdate(deleted_date)=0 and isdate(saved_date)=0 and email_type=0and isdate(archived_date)=0) p

WHERE rownum between 1 AND 20


How would I incorporate your query into that? I can't seem to get it....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-02 : 01:47:47
[code]
SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY threadid ORDER BY date_added DESC) AS Seq from (SELECT ROW_NUMBER() OVER(ORDER BY e.date_added desc) AS rownum, e.emailid, e.date_added, e.to_email, e.from_email, e.cc_emails, e.subject, e.attachments, e.threadid, e.saved_date,
e.read_date, e.read_by_userid, e.replied_date, e.forwarded_date, (select count(emailid) as c

from emails where clubid=3 and isdate(deleted_date)=0 and isdate(saved_date)=0 and email_type=0and isdate(archived_date)=0) as totrows, u.firstname + ' ' + u.lastname as name
FROM emails e LEFT JOIN users u ON e.userid = u.user_id

where e.clubid=3 and isdate(deleted_date)=0 and isdate(saved_date)=0 and email_type=0and isdate(archived_date)=0) p

WHERE rownum between 1 AND 20
)r
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bpsintl
Posting Yak Master

132 Posts

Posted - 2013-02-04 : 03:30:47
Brilliant, works great. Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-04 : 03:38:05
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -