LAG/LEAD Demo for SQL Server

-- This demo can only be run on SQL SERVER 2012 or higher because of the LAG/LEAD function calls
USE <database_name>;
GO
CREATE TABLE #LagLeadSource
(
    ID INT IDENTITY(1, 1) NOT NULL ,
    Value INT NOT NULL
);

INSERT INTO #LagLeadSource
( Value )
VALUES ( 123 ),( 234 ),( 345 ),( 456 ),( 567 ),( 678 ),( 789 ),( 890 ),( 901 );

SELECT ID,
       LAG(Value, 4, 0) OVER ( ORDER BY ID ASC) AS Next_x_4_LowestValue,
       LAG(Value, 3, 0) OVER ( ORDER BY ID ASC) AS Next_x_3_LowestValue,
       LAG(Value, 2, 0) OVER ( ORDER BY ID ASC) AS Next_x_2_LowestValue,
       LAG(Value, 1, 0) OVER ( ORDER BY ID ASC) AS NextLowestValue,
       Value AS 'ACTUAL_VALUE',
       LEAD(Value, 1, 0) OVER ( ORDER BY ID ASC) AS NextHighestValue,
       LEAD(Value, 2, 0) OVER ( ORDER BY ID ASC) AS Next_x_2_HighestValue,
       LEAD(Value, 3, 0) OVER ( ORDER BY ID ASC) AS Next_x_3_HighestValue,
       LEAD(Value, 4, 0) OVER ( ORDER BY ID ASC) AS Next_x_4_HighestValue
FROM #LagLeadSource
-- using a WHERE clause here will "break" this code as the dataset will have a row-restriction applied before the LAG/LEAD functions are ever called, rendering them useless

IF OBJECT_ID('tempdb..#LagLeadSource') IS NOT NULL
DROP TABLE #LagLeadSource;

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