SQLcommitted

Committed with SQL

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

Output:

image

 

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

Solution Using Lag function:

image

 

image

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:

image

image

 

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.

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

About these ads

January 11, 2013 - Posted by | SQL SERVER 2012, Transact-SQL | , , , , , , , ,

1 Comment »

  1. Nice blog with nice explanation…

    Comment by sanmaya sahoo | January 11, 2013 | Reply


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

Follow

Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: