Analytical SQL Queries: Return one row for maximum date

create table Customer

(

CustomerID int identity(1,1) primary key,

FirstName varchar(50),

LastName varchar(50)

)

go

create table CustomerVisit

(

CustomerID int,

VisitTime datetime,

Duration numeric(3,2)

)

go

insert into Customer (FirstName, LastName) VALUES ('S1', 'J1')

insert into Customer (FirstName, LastName) VALUES ('S2', 'J2')

select * from Customer

CustomerID FirstName LastName

--------------------------------

1 S1 J1

2 S2 J2

insert into CustomerVisit (CustomerID, VisitTime, Duration) VALUES (1, getdate()-2, 2)

insert into CustomerVisit (CustomerID, VisitTime, Duration) VALUES (1, getdate()-3, 4)

insert into CustomerVisit (CustomerID, VisitTime, Duration) VALUES (1, getdate()-5, 1)

go

select * from CustomerVisit

CustomerID VisitTime Duration

----------------------------------------------------

1 2011-08-15 14:33:58.060 2.00

1 2011-08-14 14:34:18.287 4.00

1 2011-08-12 14:34:18.287 1.00

CustomerID FirstName LastName MAX(VisitTime) Duration

---------------------------------------------------------------------------

1 S1 J1 2011-08-15 14:33:58.060 2.00

SELECT Customer.CustomerID,FirstName,LastName,X.Duration, X.VisitTime

FROM Customer

INNER JOIN CustomerVisit ON CustomerVisit.CustomerID = Customer.CustomerID

INNER JOIN

(SELECT CustomerID, Duration, MAX(VisitTime) VisitTime FROM CustomerVisit GROUP BY CustomerID,Duration)

X ON X.CustomerID = CustomerVisit.CustomerID AND X.VisitTime = CustomerVisit.VisitTime

SELECT CUSTOMER.CustomerID, Customer.FirstName, Customer.LastName,Duration, VisitTime

from Customer

LEFT OUTER JOIN

(

select CustomerID,

Duration,

VisitTime,

max(VisitTime) over (partition by CustomerID) MAX_DATE

from CustomerVisit

)X ON X.CustomerID = Customer.CustomerID AND VisitTime = X.MAX_DATE