inforakesha

A fine WordPress.com site

SQL Server – New Analytical functions in SQL Server 2012 – Lead and Lag

Leave a comment

SQL Server – New Analytical functions in SQL Server 2012 – Lead and Lag

LAG:

Using this function we can fetch the nth row before (Lag) the Current row without using self-join.

It is useful when we need to compare the current row values with values in a previous row.

Syntax:

LAG (scalar_expression [,offset] [,default])

OVER ( [ partition_by_clause ] order_by_clause )

EXAMPLE:

Create an Employee table and insert some records to it.

CREATE TABLE Employee (EmpID int, HikeDate date, Salary money)
GO
INSERT INTO employee values (1, ‘2009-04-01 00:00:00.000’,10000),
(1, ‘2010-04-01 00:00:00.000’,17000),
(1, ‘2011-04-01 00:00:00.000’,23000)
GO

SELECT EmpID, HikeDate, Salary
FROM Employee
ORDER BY HikeDate

Run the query-

Now my requirement is to show what the previous Salary before current hike was?

Solution Using Lag function:

 

LEAD:

Using this function we can fetch the nth row after (LEAD) the current row without using self-join.

It is useful when we need to compare the current row values with values in a following row.

Syntax:

LEAD (scalar_expression [,offset] [,default])

OVER ( [ partition_by_clause ] order_by_clause )

Example:

My requirement is to show what my following year salary is?

Solution using Lead function:

Note: Default offset value is 1. We can specify offset value by a column or a sub query, or other expression that evaluates to a positive integer. Negative value or analytical function can’t be used as offset value.

EXAMPLE 2:

SQL Server 2012 introduces new analytical function LEAD() and LAG(). This functions accesses data from a subsequent row (for lead) and previous row (for lag) in the same result set without the use of a self-join . It will be very difficult to explain this in words so I will attempt small example to explain you this function. Instead of creating new table, I will be using AdventureWorks sample database as most of the developer uses that for experiment.

Let us fun following query.
USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
LEAD(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID
) LeadValue,
LAG(SalesOrderDetailID) OVER (ORDER BY SalesOrderDetailID
) LagValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO

Above query will give us following result.

When we look at above resultset it is very clear that LEAD function gives us value which is going to come in next line and LAG function gives us value which was encountered in previous line. If we have to generate the same result without using this function we will have to use self join. In future blog post we will see the same. Let us explore this function a bit more. This function not only provide previous or next line but it can also access any line before or after using offset.

Let us fun following query, where LEAD and LAG function accesses the row with offset of 2.

USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
LEAD(SalesOrderDetailID,2) OVER (ORDER BY SalesOrderDetailID
) LeadValue,
LAG(SalesOrderDetailID,2) OVER (ORDER BY SalesOrderDetailID
) LagValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO

Above query will give us following result.

You can see the LEAD and LAG functions  now have interval of  rows when they are returning results. As there is interval of two rows the first two rows in LEAD function and last two rows in LAG function will return NULL value. You can easily replace this NULL Value with any other default value by passing third parameter in LEAD and LAG function.

Let us fun following query.

USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
LEAD(SalesOrderDetailID,2,0) OVER (ORDER BY SalesOrderDetailID
) LeadValue,
LAG(SalesOrderDetailID,2,0) OVER (ORDER BY SalesOrderDetailID
) LagValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO

Above query will give us following result, where NULL are now replaced with value 0.

Just like any other analytic function we can easily partition this function as well. Let us see the use of PARTITION BY in this clause.

USE AdventureWorks
GO
SELECT s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty,
LEAD(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY SalesOrderDetailID
) LeadValue,
LAG(SalesOrderDetailID) OVER (PARTITION BY SalesOrderID
ORDER BY SalesOrderDetailID
) LagValue
FROM Sales.SalesOrderDetail s
WHERE SalesOrderID IN (43670, 43669, 43667, 43663)
ORDER BY s.SalesOrderID,s.SalesOrderDetailID,s.OrderQty
GO

Above query will give us following result, where now the data is partitioned by SalesOrderID and LEAD and LAG functions are returning the appropriate result in that window. As now there are smaller partition in my query, you will see higher presence of NULL.

In future blog post we will see how this functions are compared to SELF JOIN.

If you liked this post, do like on Facebook at https://www.facebook.com/inforakesha

Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s