sql help please - get latest activity for customer

Question

Hi All,

Given 2 tables

CustomerActivity
CustomerId, ActivityId, CreatedOnDate
1, 1, 8/1/2010
1, 2, 8/15/2010
2, 1, 7/24/2010
2, 2, 8/15/2010

TempUpdateTable
CustomerId, RecentActivityDate
1, NULL
2, NULL

How do I fill in the NULLs in TempUpdateTable using the CustomerActivity table?

My first attempt didn't pan out:

UPDATE [TempUpdateTable]
SET RecentActivityDate = 
(SELECT MAX(CreatedOnDate) FROM CustomerActivity CA WHERE CA.CustomerId = CustomerId )

Thanks,

rod.

Answer 1

Try this:

;with LatestActivity as
(
select CustomerId, max(CreatedOnDate) LastActivityDate 
from CustomerActivity ca
group by CustomerId
)
update tut
set tut.RecentActivityDate = la.LastActivityDate 
from TempUpdateTable tut
join LatestActivity la on tut.CustomerId = la.CustomerId

Answer 2

You can also try this:

UPDATE [TempUpdateTable] 
SET RecentActivityDate = Customers.MaxCreatedDate
FROM
(SELECT MAX(CreatedOnDate) as MaxCreatedDate, CA.CustomerId 
 FROM CustomerActivity CA WHERE CA.CustomerId = CustomerId 
 GROUP BY CA.CustomerId
) Customers
WHERE TempUpdateTable.CustomerId = Customers.CustomerId

Answer 3

You can use a CTE (Common Table Expression) to find the last activity for each customer:

;WITH LastActivity AS
(
    SELECT
        CustomerID, ActivityID, CreatedOnDate,
        ROW_NUMBER() OVER(PARTITION BY CustomerId ORDER BY CreatedOnDate DESC) 'RowNum'
    FROM
        dbo.CustomerActivity
)
SELECT * FROM LastActivity
WHERE RowNum = 1

This will give you one row for each customer with the activity that has the latest date. The PARTITION BY partitions your data by customer, e.g. the counter starts at 1 again for each new customer. The ORDER BY defines the descending ordering by date, so that the latest/newest activity is the first one, with row number 1.

Now you can use the CTE to update your other table:

;WITH LastActivity AS
(
    SELECT
        CustomerID, ActivityID, CreatedOnDate,
        ROW_NUMBER() OVER(PARTITION BY CustomerId ORDER BY CreatedOnDate DESC) 'RowNum'
    FROM
        dbo.CustomerActivity
)
UPDATE dbo.TempUpdateTable
SET RecentActivityDate = act.CreatedOnDate
FROM LastActivity act
WHERE dbo.TempUpdateTable.CustomerId = act.CustomerID
AND act.RowNum = 1

Answer 4

Here's a way to do it.

UPDATE TempUpdateTable
SET RecentActivityDate = ca.RecentActivityDate
FROM TempUpdateTable
JOIN
    ( SELECT CustomerId, MAX(CreatedOnDate) AS RecentActivityDate
    FROM CustomerActivity
    GROUP BY CustomerId
    ) ca ON TempUpdateTable.CustomerId = ca.CustomerId