SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Grouping emails by conversation thread
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bpsintl
Posting Yak Master

132 Posts

Posted - 01/25/2013 :  03:44:24  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 01/25/2013 :  03:56:00  Show Profile  Reply with Quote

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


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

Go to Top of Page

bpsintl
Posting Yak Master

132 Posts

Posted - 01/25/2013 :  05:30:58  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 01/25/2013 :  05:37:50  Show Profile  Reply with Quote
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 - 02/01/2013 :  05:39:02  Show Profile  Reply with Quote
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

Edited by - bpsintl on 02/01/2013 05:40:49
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 02/01/2013 :  06:35:28  Show Profile  Reply with Quote
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 - 02/01/2013 :  07:21:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/01/2013 :  11:53:05  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 02/01/2013 :  12:39:52  Show Profile  Reply with Quote

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


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

Go to Top of Page

bpsintl
Posting Yak Master

132 Posts

Posted - 02/01/2013 :  13:31:23  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 02/02/2013 :  01:47:47  Show Profile  Reply with Quote

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


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

Go to Top of Page

bpsintl
Posting Yak Master

132 Posts

Posted - 02/04/2013 :  03:30:47  Show Profile  Reply with Quote
Brilliant, works great. Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 02/04/2013 :  03:38:05  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000