LEAD function in SQL server

LEAD provides access to a row at a given physical offset that comes after the current row. Accesses data from a next row in the same result set. The LEAD () function is used to get value from row that successor of the current row. The LEAD functions can be helpful when performing various data analysis tasks that require comparing values from different rows. For instance, we can calculate the difference between the current and next values of a column, such as sales, revenue, or profit. We can also find the percentage change or growth rate of a column over time or identify the first or last value of a column within a group.

See the below example

We have Sales.CurrencyRate table in that we are getting the currency rate

SELECT currencyratedate,        fromcurrencycode,        tocurrencycode,        averagerate,        endofdayrate FROM   sales.currencyrate cr WHERE  cr.fromcurrencycode = 'USD'        AND cr.tocurrencycode = 'ARS'

  
We have requirement like we need to show the next day and next_endofdayrate. For example we need to get the result as below 

See here currencyRateDate : 2011-05-31 is having EndOfRate : 1.0002 so for the next day is 2011-06-01 and next day is 2011-06-01 and rate is .9991.

To get this result we can using sub query as below.

SELECT   currencyratedate,   fromcurrencycode,   tocurrencycode,   averagerate,   endofdayrate,   (     SELECT       TOP(1) pcr.currencyratedate     FROM       sales.currencyrate pcr     WHERE       cr.fromcurrencycode = pcr.fromcurrencycode       AND cr.tocurrencycode = pcr.tocurrencycode       AND cr.currencyratedate < pcr.currencyratedate     ORDER BY       pcr.currencyratedate   ) AS Previous_day_CurrencyRateDate,   (     SELECT       TOP(1) endofdayrate     FROM       sales.currencyrate pcr     WHERE       cr.fromcurrencycode = pcr.fromcurrencycode       AND cr.tocurrencycode = pcr.tocurrencycode       AND cr.currencyratedate < pcr.currencyratedate     ORDER BY       pcr.currencyratedate   ) AS Previous_day_EndOfDayRate FROM   sales.currencyrate cr WHERE   cr.fromcurrencycode = 'USD'   AND cr.tocurrencycode = 'ARS'

Getting the below output.

  

Getting the expected result.

Think about query, it is complex one. We can get the same result with the help of LEAD() window function.

SELECT currencyratedate,        fromcurrencycode,        tocurrencycode,        averagerate,        endofdayrate,        LEAD(currencyratedate)          OVER(            ORDER BY currencyratedate) AS next_day_CurrencyRateDate,        LEAD(endofdayrate)          OVER(            ORDER BY currencyratedate) AS next_day_EndOfDayRate FROM   sales.currencyrate WHERE  fromcurrencycode = 'USD'        AND tocurrencycode = 'ARS'

 

Got the same result.

LEAD() function is very useful in the analysis related queries.

Syntax

Use of

LEAD (<column you need>) OVER ([PARTITION BY <expression>] ORDER BY <expression>)

See below example

SELECT customerid,        salesorderid,        Cast(orderdate AS DATE)    AS OrderDate,        Lead(Cast(orderdate AS DATE))          OVER(            partition BY customerid            ORDER BY salesorderid) AS NextOrderDate FROM   sales.salesorderheader;

 

Here we are seeing several NULL values in the results. Row 3 is the last row of the partition. We cannot find a next row, so NULL is returned for NextOrderDate. In the above example we have not used offset. By default offset value is 1. If we want to access columns from rows farther away than one we can use the offset. See below

LEAD(<column> [,<offset>]) OVER ([PARTITION BY <expression>] ORDER BY <expression>)

See the below example

SELECT customerid,        salesorderid,        Cast(orderdate AS DATE)    AS OrderDate,        Lead(Cast(orderdate AS DATE),2)          OVER(            partition BY customerid            ORDER BY salesorderid) AS NextOrderDate FROM   sales.salesorderheader;

Here we are skipping 2 row. In the above example we are seeing multiple null. We can replace these null with default value. See the below syntax.

LEAD(<column> [,<offset>] [,<default>]) OVER([PARTITION BY <expression>] ORDER BY <expression>) 

SELECT customerid,        salesorderid,        Cast(orderdate AS DATE)    AS OrderDate,        Lead(Cast(orderdate AS DATE),2,'1900-01-01')          OVER(            partition BY customerid            ORDER BY salesorderid) AS NextOrderDate FROM   sales.salesorderheader;