LEAD And LAG Analytic Functions in SQL Server 2012

LEAD And LAG Functions in SQL Server 2012

LEAD And LAG provides access to a row at a given physical offset respective to current row.
Use these analytic function in a SELECT statement to compare values in the current row with values in a Previous and following row according to given offset.
Note: Offset cannot be negative.

Let us take a passengers example: 
suppose some passengers are travelling from one city to another, they are passing
through many cities, now if we want to find out which was the previous
& city which will be next city we can achieve this using Common table expression and ranking
functions as follow

-- Let us code this in SQL Server
-- Declare @Passenger Table as below.
DECLARE   @Passenger TABLE
  (
       PassengerID VARCHAR(10),
       CityName   VARCHAR(10),
       StartDate  DATE
    )
/* Insert record in Passenger table, suppose passengers are passing different cities 
on different dates as below*/

INSERT INTO @Passenger VALUES ('P00001', 'Delhi', '1-Sep-2014')
INSERT INTO @Passenger VALUES ('P00001', 'Mathura', '1-Oct-2014')
INSERT INTO @Passenger VALUES ('P00001', 'Etah', '10-Oct-2014')
INSERT INTO @Passenger VALUES ('P00001', 'Kanpur', '25-Oct-2014')
INSERT INTO @Passenger VALUES ('P00001', 'Lucknow', '27-Oct-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Allahabad', '1-Oct-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Banaras', '1-Nov-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Mumbai', '10-Nov-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Banaras', '15-Nov-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Pune', '25-Nov-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Hyd', '28-Nov-2014')
INSERT INTO @Passenger VALUES ('P00002', 'Chennai', '30-Nov-2014')
INSERT INTO @Passenger VALUES ('P00003', 'Bangalore', '1-Dec-2014')

/*Now if we want to find out where passengers were on next day and previous day we need to right below queries*/
--- Query 1
;
WITH   CTE as
(
        SELECT RN = ROW_NUMBER() OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC),   *
        FROM @Passenger
)
SELECT
        [Current Row].*,
        ISNULL(DATEADD(DAY, -1, [Next Row].StartDate), '31-Dec-2099') AS EndDate

,ISNULL([Previous Row].CityName,'NoPrv_Cty') PrvCity
,ISNULL([Next Row].CityName,'NoNxt_Cty') NextCity

FROM   CTE AS  [Current Row]
        LEFT JOIN CTE AS [Next Row] ON [Current Row].PassengerID   = [Next Row].PassengerID   AND  [Next Row].RN   = [Current Row].RN   + 1
LEFT JOIN CTE AS [Previous Row] ON [Current Row].PassengerID   = [Previous Row].PassengerID   AND  [Previous Row].RN = [Current Row].RN-1
ORDER BY [Current Row].PassengerID, [Current Row].RN;

/*But using LEAD and LAG Function we can achieve using below query*/
--- Query 2
SELECT *,
DATEADD(DAY, -1, LEAD(StartDate, 1,'01-Jan-2100')
        OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC))   AS EndDate,
LAG(Cityname, 1,'NoPrv_Cty')
OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC)   AS PreviousCity,
LEAD(Cityname, 1,'NoNxt_Cty')
OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC)   AS NextCity
FROM @Passenger

-- Queries will give result like below

PassengerID	CityName	StartDate	EndDate	PreviousCity	NextCity
P00001	Delhi	1-Sep-2014	30-Sep-2014	NoPrv_Cty	Mathura
P00001	Mathura	1-Oct-2014	9-Oct-2014	Delhi	Etah
P00001	Etah	10-Oct-2014	24-Oct-2014	Mathura	Kanpur
P00001	Kanpur	25-Oct-2014	26-Oct-2014	Etah	Lucknow
P00001	Lucknow	27-Oct-2014	31-Dec-2099	Kanpur	NoNxt_Cty
P00002	Allahabad	1-Oct-2014	31-Oct-2014	NoPrv_Cty	Banaras
P00002	Banaras	1-Nov-2014	9-Nov-2014	Allahabad	Mumbai
P00002	Mumbai	10-Nov-2014	14-Nov-2014	Banaras	Banaras
P00002	Banaras	15-Nov-2014	24-Nov-2014	Mumbai	Pune
P00002	Pune	25-Nov-2014	27-Nov-2014	Banaras	Hyd
P00002	Hyd	28-Nov-2014	29-Nov-2014	Pune	Chennai
P00002	Chennai	30-Nov-2014	31-Dec-2099	Hyd	NoNxt_Cty
P00003	Bangalore	1-Dec-2014	31-Dec-2099	NoPrv_Cty	NoNxt_Cty

/*Now if we want to find out which was the Previous to previous City and Which is Next to Next city

we need to modify our queries but in case of LEAD and LAG we just need to change off set Value

as below*/

SELECT *, 

DATEADD(DAY, -1, LEAD(StartDate, 1,'01-Jan-2100') 

        OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC))   AS EndDate,

LAG(Cityname, 2,'NoPrv_Cty') 

OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC)   AS PreviousCity,

LEAD(Cityname, 2,'NoNxt_Cty') 

OVER (PARTITION BY   PassengerID ORDER BY   StartDate ASC)   AS NextCity

FROM @Passenger

PassengerID	CityName	StartDate	EndDate	PreviousCity	NextCity
P00001	Delhi	1-Sep-2014	30-Sep-2014	NoPrv_Cty	Etah
P00001	Mathura	1-Oct-2014	9-Oct-2014	NoPrv_Cty	Kanpur
P00001	Etah	10-Oct-2014	24-Oct-2014	Delhi	Lucknow
P00001	Kanpur	25-Oct-2014	26-Oct-2014	Mathura	NoNxt_Cty
P00001	Lucknow	27-Oct-2014	31-Dec-2099	Etah	NoNxt_Cty
P00002	Allahabad	1-Oct-2014	31-Oct-2014	NoPrv_Cty	Mumbai
P00002	Banaras	1-Nov-2014	9-Nov-2014	NoPrv_Cty	Banaras
P00002	Mumbai	10-Nov-2014	14-Nov-2014	Allahabad	Pune
P00002	Banaras	15-Nov-2014	24-Nov-2014	Banaras	Hyd
P00002	Pune	25-Nov-2014	27-Nov-2014	Mumbai	Chennai
P00002	Hyd	28-Nov-2014	29-Nov-2014	Banaras	NoNxt_Cty
P00002	Chennai	30-Nov-2014	31-Dec-2099	Pune	NoNxt_Cty
P00003	Bangalore	1-Dec-2014	31-Dec-2099	NoPrv_Cty	NoNxt_Cty