Tsql Analytic functions in SqlServer2012

Tsql in sql server 2012 has  number of  new analytic functions which can be used for various reporting needs importantly  for different window frames and offsets

Some of the functions which are introduced  are

• Lag
• Lead
• First_Value
• Last_Value

Lag:  Lag  enables  to find the data from previous row without using the selfjoin  

An example of lag function using the AdventureWorksDW2012  database  ,i have created a CTE  with  the aggregate at sales territory  productcategory and CaleanderYear   and i'm using the following query  to demonstrate the use of lag as shown below

Lag function accepts two arguements ,the first arguement is the offset,  in this case i have used 1 for previous year and a default arguement to assign a default value for the row prior to the start of our window

Basically  the above query   uses  lag  function to find the previous year sales along with each calendar year   Sales and it is partitioned by sales territory and ordered by calendar year ,

The result of the query looks like below

Lead is a similar function which avoids a self join to give a leading value for the period of interest