purplesoup
asked on
SQL Server - Getting the most recent engagement for each contact
I'd like to know the most efficient way of doing the following.
I've a table with following columns:
EngagementKey uniqueidentifier (PK)
EngagementDate DateTime
ContactKey uniqueidentifier
I'd like to return a list of distinct contacts with the most recent engagement date for each contact, and the engagement key for the most recent engagement.
I have to be able to support SQL Server 2008 and later.
I've a table with following columns:
EngagementKey uniqueidentifier (PK)
EngagementDate DateTime
ContactKey uniqueidentifier
I'd like to return a list of distinct contacts with the most recent engagement date for each contact, and the engagement key for the most recent engagement.
I have to be able to support SQL Server 2008 and later.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Vitor, your solution does not satisfy this requirement:
I'd like to return a list of distinct contacts
Why not, Éric? Is grouping by Engagement and Contact and for each one of this set is returning the most recent engagement date:
SELECT EngagementKey, MAX(EngagementDate) AS LastEngagement, ContactKey
FROM TableName
GROUP BY EngagementKey, ContactKey
What I'm missing here?
if one contact has multiple engagement, your results will contain a list of distinct contacts
ASKER
I created a little example to test with:
Eric and Scott's solutions both return the same results - Vitor's returns all 7 rows.
I looked at the execution plan for Eric's and Scott's and it looks like Scott's is a little more efficient, but they both work, so I'll share the points.
CREATE TABLE Engagements
(EngagementKey UNIQUEIDENTIFIER PRIMARY KEY,
ContactKey UNIQUEIDENTIFIER FOREIGN KEY REFERENCES ContactMain(ContactKey),
EngagementDate DATETIME)
INSERT INTO dbo.Engagements
( EngagementKey ,
ContactKey ,
EngagementDate
)
VALUES ( NEWID(),
'208AA47D-EA06-4088-BDDB-000CF2DDACE4',
'20160411'
),
( NEWID(),
'7D15EE79-17AF-4D13-9694-0018947AA9E4',
'20160616'
),
( NEWID(),
'208AA47D-EA06-4088-BDDB-000CF2DDACE4',
'20150811'
),
( NEWID(),
'208AA47D-EA06-4088-BDDB-000CF2DDACE4',
'20140811'
),
( NEWID(),
'7D15EE79-17AF-4D13-9694-0018947AA9E4',
'20161115'
),
( NEWID(),
'208AA47D-EA06-4088-BDDB-000CF2DDACE4',
'20161216'
),
( NEWID(),
'4CEDAD08-738F-412F-A581-0020AD9518E4',
'20160201'
)
Eric and Scott's solutions both return the same results - Vitor's returns all 7 rows.
I looked at the execution plan for Eric's and Scott's and it looks like Scott's is a little more efficient, but they both work, so I'll share the points.
ASKER
Thanks for answering so quickly!
if one contact has multiple engagement, your results will contain a list of distinct contactsI see. Looks like I misunderstood the question. Anyway, it seems that the author already got an answer :)
Open in new window