Home > SQLServer, SQLServer 2012 > SQL Server – "Denali" – Analytic Functions – LAG() and LEAD()

SQL Server – "Denali" – Analytic Functions – LAG() and LEAD()

LAG() function can be used to access data from a previous row in the result set without using a self-join. And it’s counterpart LEAD() can be used to access data from a subsequent row in the same result set. These functions are introduced in “Denali” as T-SQL functions, these has been available as MDX functions since SQL Server 2005.

LAG() and LEAD() functions are also available in Oracle since Oracle 8i.

Syntax for LAG()/LEAD():

LAG/LEAD (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

offset provides the numbers of rows back or forward the current row. default provides a default value when the scalar_expression returned by offset is NULL.

Below example uses LEG() and LEAD() to identify previous or next date for a particular order.

CREATE TABLE #Orders

(

       OrderDate     DATE,

       ProductID     INT,

       Quantity      INT

)

INSERT INTO #Orders VALUES

('2011-07-28',11,12), ('2011-03-18',12,74), ('2011-04-12',13,95),

('2011-07-25',14,57), ('2011-05-30',11,28), ('2011-05-21',10,12),

('2011-04-12',11,38)

 

SELECT OrderDate, ProductID, Quantity

FROM   #Orders

Result Set:

OrderDate  ProductID   Quantity

———- ———– ———–

2011-07-28 11          12

2011-03-18 12          74

2011-04-12 13          95

2011-07-25 14          57

2011-05-30 11          28

2011-05-21 10          12

2011-04-12 11          38

 

(7 row(s) affected)

Last order date using LAG():

SELECT OrderDate, ProductID, Quantity,

       LAG(OrderDate, 1, OrderDate)

       OVER (PARTITION BY ProductID ORDER BY OrderDate)

       AS Last_OrderDate

FROM   #Orders

Result Set:

OrderDate  ProductID   Quantity    Last_OrderDate

———- ———– ———– ————–

2011-05-21 10          12          2011-05-21

2011-04-12 11          38          2011-04-12

2011-05-30 11          28          2011-04-12

2011-07-28 11          12          2011-05-30

2011-03-18 12          74          2011-03-18

2011-04-12 13          95          2011-04-12

2011-07-25 14          57          2011-07-25

 

(7 row(s) affected)

Next order date using LEAD():

SELECT OrderDate, ProductID, Quantity,

       LEAD(OrderDate, 1, OrderDate)

       OVER (PARTITION BY ProductID ORDER BY OrderDate)

       AS Next_OrderDate

FROM   #Orders

Result Set:

OrderDate  ProductID   Quantity    Next_OrderDate

———- ———– ———– ————–

2011-05-21 10          12          2011-05-21

2011-04-12 11          38          2011-05-30

2011-05-30 11          28          2011-07-28

2011-07-28 11          12          2011-07-28

2011-03-18 12          74          2011-03-18

2011-04-12 13          95          2011-04-12

2011-07-25 14          57          2011-07-25

 

(7 row(s) affected)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

Advertisements
Categories: SQLServer, SQLServer 2012
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: