Lag:-New function in sql server denali

LAG

We all have read about LEAD in previous blog . LAG is working opposite of LEAD where we can go backword in dataset.

We can get value from previous subsequent row with given offset value without using a self join or CTE



We can use this function to compare  value with other row in the Table for analysis purpose

Syntax

LAG ( scalar_expression ,offset , default )

    OVER ( Parition By,Order By)

Scalar Expression :- Any scalar expression allowed in SQL Server

Offset:- Number of rows forward from the current row

Default:- Default value if no row find at given Offset location  from current row

Example

Lets see we want to compare order quantity of each workorder with its previous workorder

SELECT Lag(orderqty, 1, 0) OVER (ORDER BY workorderid) nextorderqty,

       orderqty,

       *

FROM   production.workorder

Whate we can see here is that first row does not have any pervious subsequent row. so it will display defualt 0

Use of Partition by function

Now we want to compare order quantity but in each ProductID

SELECT Lag(orderqty, 1, 0) OVER (partition BY productid ORDER BY workorderid) nextorderqty,

       orderqty,

       *

FROM   production.workorder

Here first order in each ProductID  will have default value 0 because it doesnt have next record in the same ProductID