SQL Server 2012: Getting Previous and Next values using Offset functions

How can we access other rows in a set while accessing one particular row? In other words, can we access values in other rows other than current row while the current row is being processed? There were no built-in functions for supporting this functionality with previous versions of SQL Server but there were many ways of getting the required result generated. One common way was linking the same table to itself either using as a derived table or CTE. Microsoft SQL Server offers four offset functions for supporting this requirement. Here is note on it;

SQL Server 2012 Offset Functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE
Offset functions allow to access values located in other rows while accessing the current row;

Function	Description
LAG	LAG works on window partition and window order clauses. It allows to access a value of a row at a certain offset from the current row which appears before the current row based on the order specified. It accepts three parameters; value (or column) which needs to be returned, offset as optional (1 is default), and default value to be returned in case of no row at the specified offset (null is default).
LEAD	LEAD works on same manner, just like LAG. Only different is, while LAG is looking for records before the current row, LEAD is looking for records after the current row.
FIRST_VALUE	This allows to access values from the first row in the window frame. The first value of the first row is accessed with a window frame extent ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
LAST_VALUE	This allows to access values from the last row in the windows frame. The extent ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING should be used with LAST_VALUE.

Here is an example for offset functions;

1. USE AdventureWorks2012
2. GO
3.  
4. -- Create a view for forming a set that contains sales amount for years and months.
5. CREATE VIEW dbo.SalesView
6.     AS
7.     SELECT YEAR(h.OrderDate) OrderYear, MONTH(OrderDate) OrderMonth
8.         , SUM(h.SubTotal) Total
9.     FROM Sales.SalesOrderHeader h
10.     GROUP BY YEAR(h.OrderDate), MONTH(OrderDate)
11. GO
12.  
13.  
14. SELECT
15.     OrderYear, OrderMonth, Total
16.     -- Getting previous month value for the year
17.     , LAG(Total, 1, 0) OVER (PARTITION BY OrderYear ORDER BY OrderMonth) PreviousMonthTotal
18.     -- Getting next month value for the year
19.     , LEAD(Total, 1, 0) OVER (PARTITION BY OrderYear ORDER BY OrderMonth) NextMonthTotal
20.     -- Getting first month value for the year
21.     , FIRST_VALUE(Total) OVER (PARTITION BY OrderYear ORDER BY OrderMonth ROWS BETWEEN UNBOUNDED PRECEDING
22. AND CURRENT ROW) FirstMonthValue
23.     -- Getting last month value for the yea
24.     , LAST_VALUE(Total) OVER (PARTITION BY OrderYear ORDER BY OrderMonth ROWS BETWEEN CURRENT ROW
25. AND UNBOUNDED FOLLOWING) LastMonthValue
26. FROM dbo.SalesView
27. ORDER BY 1, 2