• There are certainly many ways to accomplish this and end up with the same results---but performance is the key in deciding what to do. LEAD and LAG are very, very efficient---and their value increases if you need other window functions over the same window, as the window itself can be shared across each.

    Definitely test each case to determine how many seeks/scans are being performed and how IO looks---especially if you are upgrading to SQL Server 2012 and have new tools at your disposal that can be applied to older code.

    A correlated subquery or cursor/WHILE solution may be adequate for small data sets, but data can change over time, so caution should be exercised before making changes now that could end up being a hindrance in the future.