Need something better than a sub-query

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

Transact-SQL (2005)

Need something better than a sub-query

Author

Topic

Seraphi
Starting Member

5 Posts

Posted - 2010-03-12 : 09:12:04

Hey guys, I'm writing a system that will record activity (both inbound and outbound) between sales and customers and I'm trying to produce a report that I could use a bit of help with!

The Tables (I've heavily modified these to make the example as simple as possible):


Activities

ID |Purpose |Subject       |UserID |DateCreated
1  |None    |Test Activity |1      |01/01/2010 00:00:00


ActivityComments

ID |ActivityID |Method   |Text           |UserID |DateCreated
1  |1          |Inbound	 |Test Message 1 |1      |01/01/2010 00:00:00
2  |1          |Inbound	 |Test Message 2 |1      |01/01/2010 01:00:00
3  |1          |Outbound |Test Message 3 |1      |01/01/2010 02:00:00

The Problem:

I'm trying to write a report that shows a list of all activities and against all these activities I'd like to show the latest inbound activty comment and the ID of that comment. I tried doing this by doing a sub-query within the SELECT query but I think this is inefficient and wondered if there is a better way to get my desired output. I've also tried left outer joining to the comments table and then grouping by everything except the date and then running a MAX() on the comment's datecreated, this one works fine for the date but not other information about the comment I may want to return i.e. the UserID, not to mention the potentiall slow speed with lots of results!

This is my required output:


List of Activities (results from query):

ID |UserID |Purpose |Subject       |ActivityCreated     |LastInboundDate     |LastInboundCommentID
1  |1      |None    |Test Activity |01/01/2010 00:00:00 |01/01/2010 01:00:00 |2

The Solution:

I need all the help I can get, I've spent all morning trying to find a solution and worked my way in to two dead-ends! Thanks in advance! :)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-12 : 09:23:46

[code]
select *
from
(
select *, row_no = row_number() over (partition by c.ID order by c.DateCreated desc)
from Activity a
inner join ActivityComments c on a.ID = c.ActivityID
where c.Method = 'Inbound'
) a
where a.row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-12 : 09:53:32

quote:

Originally posted by khtan


select *
from
(
select *, row_no = row_number() over (partition by c.ActivityID order by c.DateCreated desc)
from   Activity a
       inner join ActivityComments c on a.ID = c.ActivityID 
where  c.Method   = 'Inbound'
) a
where a.row_no = 1

KH
[spoiler]Time is always against us[/spoiler]

Tan...Shouldn't it be this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-12 : 10:02:12

you are right. should be c.ActivityID

KH
[spoiler]Time is always against us[/spoiler]

Seraphi
Starting Member

5 Posts

Posted - 2010-03-12 : 10:42:49

Thanks for the replies guys!

I've tried this query out and it does indeed produce produce the results my example was expecting, however... there seems to be a problem with my example, doh! The aim of the report is to show ALL the activities in the system and if they have had an inbound then it needs to show the latest inbound date. The problem with your solution is that it only seems to show activities that have had an inbound against them which may not always be the case.

Take a look at the new example (sorry):

The Tables:


Activities

ID |Purpose |Subject         |UserID |DateCreated
1  |None    |Test Activity   |1      |01/01/2010 00:00:00
2  |Sale    |Test Activity 2 |1      |02/01/2010 00:00:00


ActivityComments

ID |ActivityID |Method   |Text           |UserID |DateCreated
1  |1          |Inbound	 |Test Message 1 |1      |01/01/2010 00:00:00
2  |1          |Inbound	 |Test Message 2 |1      |01/01/2010 01:00:00
3  |1          |Outbound |Test Message 3 |1      |01/01/2010 02:00:00
4  |2          |Outbound |Test Message 4 |1      |02/01/2010 00:00:00

The Required Output (note there are no inbound comments for activity 2 but it is still returned):


ID |UserID |Purpose |Subject         |ActivityCreated     |LastInboundDate     |LastInboundCommentID
1  |1      |None    |Test Activity   |01/01/2010 00:00:00 |01/01/2010 01:00:00 |2
2  |1      |Sale    |Test Activity 2 |02/01/2010 00:00:00 |NULL                |NULL

Thanks again and sorry for the first example! ;)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-12 : 10:48:01

Change the 'inner join' in the query to a 'left join'

Seraphi
Starting Member

5 Posts

Posted - 2010-03-12 : 10:53:52

Hmm, tried that and it's still only returning activities that have an inbound comment against them...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-12 : 10:59:33

for LEFT JOIN you will need to partition by a.ID as c.ActivityID will be NULL for those record in Activity table not in Activity Comments table. Also move the "Inbound" condition to the JOIN


select *
from
(
select < specify the required column list here>, 
       row_no = row_number() over (partition by a.ID order by c.DateCreated desc)
from   Activity a
       left join ActivityComments c on a.ID = c.ActivityID 
                                 and c.Method   = 'Inbound'
) a
where a.row_no = 1

KH
[spoiler]Time is always against us[/spoiler]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-12 : 11:03:53

Maybe there's a bettwe way...but this?

select a.ID,a.purpose,a.subject,a.ActivityCreated,(case when a.method = 'Inbound' then a.LastInboundDate else null end) as LastInboundDate,(case when a.method = 'Inbound' then a.LastInboundCommentID else null end) as LastInboundCommentID
from
(
select a.ID,a.purpose,a.subject,a.datecreated as  ActivityCreated,c.datecreated as LastInboundDate, c.activityid as LastInboundCommentID,c.method,row_no = row_number() over (partition by c.ActivityID order by c.Method,c.DateCreated desc)
from   Activity a
       left join ActivityComments c on a.ID = c.ActivityID 
) a
where a.row_no = 1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-03-12 : 11:05:06

Yeah..Tan's much better.

Seraphi
Starting Member

5 Posts

Posted - 2010-03-12 : 11:25:43

Guys... brilliant! :)

khtan, your solution works like a dream, thank-you so much!

I have one more question... How would this work for an additional set of columns? For example, as well as showing the LastInboundDate as you have solved above, I would also like to do this for the LastOutboundDate and have a column showing the latest outbound comment date, is this possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 11:44:37

[code]
SELECT a.ID,
a.UserID,
a.Purpose,
a.Subject,
a.DateCreated AS ActivityCreated,
ac1.DateCreated AS LastInboundDate,
ac1.ID AS LastInboundCommentID,
ac2.DateCreated AS LastOutboundDate,
ac2.ID AS LastOutboundCommentID
FROM Activities a
OUTER APPLY (SELECT TOP 1 ID,DateCreated
FROM ActivitiesComment
WHERE ActivityID = a.ID
AND UserID = a.UserID
AND Method='Inbound'
ORDER BY DateCreated DESC) ac1
OUTER APPLY (SELECT TOP 1 ID,DateCreated
FROM ActivitiesComment
WHERE ActivityID = a.ID
AND UserID = a.UserID
AND Method='Outbound'
ORDER BY DateCreated DESC) ac2
[/code]

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

Seraphi
Starting Member

5 Posts

Posted - 2010-03-12 : 12:12:19

Hey visakh16, thanks for the reply! This solution does produce the correct results but I get the feeling it's going to be rather slow when we have lots of data to deal with? I've not actually used OUTER APPLY before, but this does look a lot like this solution is doing sub-queries, effectively doing two separate queries for every row returned in our main query, is that the case or am I reading this completely wrong? :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 12:16:59

quote:
Originally posted by Seraphi

Hey visakh16, thanks for the reply! This solution does produce the correct results but I get the feeling it's going to be rather slow when we have lots of data to deal with? I've not actually used OUTER APPLY before, but this does look a lot like this solution is doing sub-queries, effectively doing two separate queries for every row returned in our main query, is that the case or am I reading this completely wrong? :(

Try it out on large data set and test how it performs

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources