Heh, it looks like Lynn and I had identical thoughts 🙂

I originally didn't even mention this since the question was posted in the 2008 forum, but just on the off-chance that you are running 2012, LEAD and LAG were introduced for just such a situation.

With the same sample data as before, the query would look like this:

SELECT

FirstLoc.ID,

FirstLoc.AssID,

FirstLoc.LocID,

StartDate=FirstLoc.PlacementDate,

EndDate=LEAD(PlacementDate,1,GETDATE()) OVER (PARTITION BY AssID ORDER BY PlacementDate ASC)

FROM AssetPlacements FirstLoc

It looks nicer, and sometimes even gives a nice performance gain.

Cheers!