Using The LEAD() And/Or LAG() Functions

Heyyy, sliding in at the eleventh hour (as usual) we have the first SQL Something for 2016!
Hard to believe that I've actually managed to, sort of, maintain this for 3 years.

Anyhow, we end the year with a couple functions that follow a similar format to RANK(): LEAD()/LAG().

LEAD() and LAG() allow users to list the value of the next row or previous row respectively with some given offset from the current row.

Simple example time.


Say you have a table with some daily transactions:

Fig. 1: Daily transactions. Obviously important business...

Say you wish to pull up data in an easily comparable way. Maybe you want to compare the transactions from a day before the transactions in a current row, but in the same row.


You would be able to do something like that with the LAG() function:

SELECT  [File_ID]
                ,[Tran_ID]
                ,[Message] AS 'Current_Transaction'
                ,LAG([Message], 1, 'No Transaction')
                         OVER (ORDER BY DAY([Date])) AS 'Previous_Transaction'
                ,[Date] AS 'Current_Date'
FROM    [N_Files];


The above results in the below:

Fig. 2: Current transactions and those that came before...

Now for the explanation. Let us look at the line with the LAG() function:
LAG([Message], 1, 'No Transaction') OVER (ORDER BY DAY([Date])) AS 'Previous_Transaction'


'LAG' accepts: 

• A Column or expression
• An offset (a positive value
• An optional default value for if, in the case of LAG, there is no previous value. The opposite is for LEAD, that is, if there is no leading value

The function is then applied over some measure of grouping, in this case the Date field.

LEAD() is very similar, where the major difference is that it handles leading values.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)