I came across this query many times and most often we need the previous row result in the current row for comparison purposes. Fortunately, we do have a solution for this problem using self join. But I will share another efficient solution with you using LAG (an analytic function shipped with SQL Server 2012).
Let me create a sample to demonstrate the solution.
Let me show you both old and new approaches.
Old Approaches :
Given below is the old approach that we generally develop using self join. This approach you can use in any version of SQL Server.
New Approaches :
In this approach, you do not need to do self join and make it complicated. You just need to use LAG function and it will calculate the previous result row for you automatically. This approach can be used in SQL Server 2012 and above.
View original post 22 more words