Link to home
Start Free TrialLog in
Avatar of purplesoup
purplesoupFlag for United Kingdom of Great Britain and Northern Ireland

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.
SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I think this should be a simple GROUP BY select:
SELECT EngagementKey, MAX(EngagementDate) AS LastEngagement, ContactKey
FROM TableName
GROUP BY EngagementKey, , ContactKey

Open in new window

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

Open in new window

What I'm missing here?
if one contact has multiple engagement, your results will contain a list of distinct contacts
Avatar of purplesoup

ASKER

I created a little example to test with:

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'
        )

Open in new window


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.
Thanks for answering so quickly!
if one contact has multiple engagement, your results will contain a list of distinct contacts
I see. Looks like I misunderstood the question. Anyway, it seems that the author already got an answer :)