Friday, February 21, 2014

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

image

No comments: