CODEWITHSUNDEEP
sqlsql-servert-sql
Rod
Rod

Reputation: 15455

sql help please - get latest activity for customer

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.

Upvotes: 2

Views: 598

Answers (5)

nkrishna
nkrishna

Reputation: 25

Though it is a bit late, maybe this is a simple answer!

SELECT ProjectID, StoreID, MAX(Date) AS Expr1, MAX(ActivityID) AS Expr2
FROM dbo.ProjectUpdates
GROUP BY ProjectID, StoreID

This gives the last activity done in a store project wise.

Upvotes: 0

bobs
bobs

Reputation: 22194

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

Upvotes: 2

marc_s
marc_s

Reputation: 754618

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

Upvotes: 3

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

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

Upvotes: 2

Denis Valeev
Denis Valeev

Reputation: 6015

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

Upvotes: 2

Related Questions