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.INBOXid, date_received, from_email, subject, threadidSENT_EMAILSid, date_sent, to_email, subject, threadidThere 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 emailcountFROM(SELECT *,ROW_NUMBER() OVER (PARTITION BY threadid ORDER BY date_received DESC) AS Seq,COUNT(1) OVER (PARTITION BY threadid) AS CntFROM INBOX)tOUTER APPLY (SELECT COUNT(1) AS Cnt FROM Sent_Emails WHERE threadid=threadid )sWHERE Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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_type4893 CF9AE779-2C34-41B0-9B7E-E2592AE00EAA 04/02/2012 xxxx xxxxx Re: Regarding the trial request form you submitted CF9AE779-2C34-41B0-9B7E-E2592AE00EAA 04898 EC239259-E69F-481A-842E-58FC91B401AA 05/02/2012 xxxx xxxxx Re: Regarding the trial request form you submitted CF9AE779-2C34-41B0-9B7E-E2592AE00EAA 04899 541AE752-EF27-4370-9D78-D8B2446CBEC2 05/02/2012 xxxx xxxxx Re: Regarding the trial request form you submitted CF9AE779-2C34-41B0-9B7E-E2592AE00EAA 14917 F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 05/02/2012 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 05019 5DFBF5FF-62D9-41AB-9EDF-073B8AB8BF2B 07/02/2012 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 15056 15C170F4-40BF-48DD-9D58-E07CA963693D 08/02/2012 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 15059 BB1AAC3F-0019-4E5A-A53A-AB7979ED56E9 08/02/2012 xxxx xxxxx Re: Regarding the trial request form you submitted BB1AAC3F-0019-4E5A-A53A-AB7979ED56E9 05072 D375148B-C357-49F5-BDD4-C3844AD119B2 08/02/2012 xxxx xxxxx Re: Regarding the trial request form you submitted BB1AAC3F-0019-4E5A-A53A-AB7979ED56E9 15074 D674D2C0-5299-4A49-A3EC-4DD6F4B9BD5F 08/02/2012 xxxx xxxxx Re: Regarding the trial request form you submitted BB1AAC3F-0019-4E5A-A53A-AB7979ED56E9 05124 455D2C51-76A9-4D8F-9A4D-4938C272CED9 10/02/2012 xxxx xxxxx website query 455D2C51-76A9-4D8F-9A4D-4938C272CED9 05129 F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 10/02/2012 xxxx xxxxx Re: half term training F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 05136 99B1137C-CC4B-49D8-AC44-D21E8971A72C 10/02/2012 xxxx xxxxx Re: half term training F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 15140 F0144C14-350F-45B1-971B-435C834C1B9D 11/02/2012 xxxx xxxxx Re: CREDIT FOR CANCELLED SESSION F0144C14-350F-45B1-971B-435C834C1B9D 05141 DEE3C64B-FD04-4119-BC34-3659EF30F5AB 11/02/2012 xxxx xxxxx Re: CREDIT FOR CANCELLED SESSION F0144C14-350F-45B1-971B-435C834C1B9D 15144 6BCBEA9F-BD0F-4494-B14D-65A9391F32EB 11/02/2012 xxxx xxxxx RE: CREDIT FOR CANCELLED SESSION F0144C14-350F-45B1-971B-435C834C1B9D 1 |
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
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_type4893 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 04898 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 04899 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 14917 F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 05/02/2012 15:42 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 05019 5DFBF5FF-62D9-41AB-9EDF-073B8AB8BF2B 07/02/2012 11:02 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 15056 15C170F4-40BF-48DD-9D58-E07CA963693D 08/02/2012 10:33 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 15059 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 05072 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 15074 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 05124 455D2C51-76A9-4D8F-9A4D-4938C272CED9 10/02/2012 15:12 xxxx xxxxx website query 455D2C51-76A9-4D8F-9A4D-4938C272CED9 05129 F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 10/02/2012 18:52 xxxx xxxxx Re: half term training F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 05136 99B1137C-CC4B-49D8-AC44-D21E8971A72C 10/02/2012 22:24 xxxx xxxxx Re: half term training F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 15140 F0144C14-350F-45B1-971B-435C834C1B9D 11/02/2012 10:12 xxxx xxxxx Re: CREDIT FOR CANCELLED SESSION F0144C14-350F-45B1-971B-435C834C1B9D 05141 DEE3C64B-FD04-4119-BC34-3659EF30F5AB 11/02/2012 10:16 xxxx xxxxx Re: CREDIT FOR CANCELLED SESSION F0144C14-350F-45B1-971B-435C834C1B9D 15144 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 05129 F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 10/02/2012 18:52 xxxx xxxxx Re: half term training F0F45B5E-4F97-4AF0-B0DC-D3B3B4A26FBC 05124 455D2C51-76A9-4D8F-9A4D-4938C272CED9 10/02/2012 15:12 xxxx xxxxx website query 455D2C51-76A9-4D8F-9A4D-4938C272CED9 05074 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 04917 F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 05/02/2012 15:42 xxxx xxxxx website query F68B23D1-F57A-43D8-9C16-76A6F0BFCB44 04898 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. |
|
|
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 |
|
|
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_typeFROM(SELECT ROW_NUMBER() OVER (PARTITION BY threadid ORDER BY date_added DESC) AS Seq,*FROM tablenameWHERE email_type =0)tWHERE Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
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.... |
|
|
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 )rWHERE Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
bpsintl
Posting Yak Master
132 Posts |
Posted - 2013-02-04 : 03:30:47
|
Brilliant, works great. Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-04 : 03:38:05
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|