CREATE TABLE dbo.Test (ClientNo int, AddDate Date, Name varchar(20)) INSERT INTO dbo.Test VALUES (1,'05/05/2014', 'Michel Fournier'), (1,'05/06/2014', 'Michel Fournier'), (1,'06/15/2014', 'James Bond'), (1,'07/01/2014', 'James Bond'), (1,'10/10/2014', 'King Kong') , (2,'05/05/2014', 'Kevin Goff'), (2,'06/15/2014', 'Katy Goff'), (2,'07/01/2014', 'Katy Goff'), (2,'10/10/2014', 'John Smith')Here's one solution with a CTE and then 2 correlated subqueries. If there's a risk of a tie (2 names for the same date), you could do a SELECT TOP 1 Name
;WITH TempCTE AS (SELECT ClientNo, MAX(AddDate) as MaxDate, MIN(AddDate) as MinDate FROM dbo.Test GROUP BY ClientNo) SELECT TempCTE.ClientNo , (SELECT Name from dbo.Test WHERE TempCTE.ClientNo = Test.ClientNo AND Tempcte.MinDate = Test.AddDate) as FirstName, (SELECT Name from dbo.Test WHERE TempCTE.ClientNo = Test.ClientNo AND Tempcte.MaxDate = Test.AddDate) as LastName FROM TempCTE