Lead and Lag in SQL Server to check the subsequent and previous value of the column

The Lead and Lag functions returns subsequent row for the Lead() while previous row for the Lag function.

Syntax

Lead(Column, OFFSET ?(OPTIONAL),DEFAULT VALUE) OVER (ORDER BY CLAUSE)

LAG (Column, OFFSET ?(OPTIONAL),DEFAULT VALUE) OVER (ORDER BY CLAUSE)

Column :

      1. Column specifies the column value to be returned or expression to return.

OFFSET :

      1. The number of rows forward from the current row from the first row. Default value for offset is 1.

Default value:

    1. The default value is set if LEAD() returns NULL.

Here we have below mentioned table records which contains Project details and their respective Business Stakeholders.

1

So Now we want to see the next project that is needed to be done once the subsequent project gets completed.

So now i want to see my subsequent project with the use of LEAP() function as shown below:

1

Using LAG() to get the previous row from the column as shown below:

1

Setting default value to the LAG and LEAD() when functions returns NULL.

We have discussed OFFSET ?(OPTIONAL),DEFAULT VALUE) in syntax. The offset value defines the number of rows forward or back with LEAD() and LAG(). While Default defines the value when function returns NULL. So in order to handle the same we will set default value ‘NoProject’

1

Happy Learning

1

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.