Analytical functions – 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 )

Lets explore it. Before exploring let us first create a table and insert some records.

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

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

Solution Using Lag function:

lag1 lag2

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?

lead1 lead2

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

Avatar

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published. Required fields are marked *