Returning top n activities for all customers

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)

Returning top n activities for all customers

Author

Topic

lisakrebs
Starting Member

10 Posts

Posted - 2010-07-09 : 16:57:18

I have 2 tables customer and activity and for each customer I need to pull the top 3 activities. It is possible that the customer does not have a record in the activity table but I still want the customer to appear in the result set. The results will eventually be used in a Business objects report.

Customer Table:
Customer ID
Name
Address

Activity table:
Activity id
customer id
activity name
activity start date
activity end date

Any assistance would be greatly appreciated,
Lisa

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-07-09 : 17:45:47

Use something like this:


SELECT C.customer_id, C.name, C.address, A.activity_id, A.activity_name, A.activity_start_date, A.activity_end_date
  FROM Customer AS C
       OUTER APPLY
       (SELECT TOP(3) A.activity_id, A.activity_name, A.activity_start_date, A.activity_end_date
          FROM Activity AS A
         WHERE A.customer_id = C.customer_id
         ORDER BY activity_start_date
                  /* replace activity_start_date with the column(s) that the TOP will use to
                     decide the order of activities and get the top 3 of them.*/) AS A

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-09 : 17:50:34

so how do you define top 3? is it the count of activities or the most recent 3? so for example last three days the user could have 50 of the same activity, but 4 days ago user had 500 of the same activity

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

lisakrebs
Starting Member

10 Posts

Posted - 2010-07-12 : 12:54:12

The most recent 3, so if a customer had 2 activities today and 3 yesterday I would pull all the activities from today and just the last one from yesterday. Does that make sense?

I tried the query provided and it worked. Thanks for everyone's help.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-07-12 : 13:27:05

you should answer yosiasz's question before you star implenting a solution. Change dense_rank() to rank(), depending on how you answer his question.

SELECT
c.CustomerID,c.cName,c.Address,t1.activityid
FROM
@customer c
LEFT JOIN
(
select customerid,activityid,[rank] = dense_rank() over(partition by customerid order by startdate desc)
from @activity
) t1
ON
c.customerid = t1.customerid
WHERE t1.rank < 4

Jim

Everyday I learn something that somebody else already knew

lisakrebs
Starting Member

10 Posts

Posted - 2010-07-12 : 14:17:44

Thanks everyone! Both queries supplied bring back the correct results I am looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources