SQL LAG, NEXT ROW EXAMPLE

15. SQL LAG, NEXT ROW EXAMPLE

create table   #CustomerPlan
  (
       CustomerCode VARCHAR(10),
         PlanCode   VARCHAR(10),
       StartDate  DATE
    )
INSERT INTO #CustomerPlan VALUES ('C00001', 'P00001', '1-Sep-2014')
INSERT INTO #CustomerPlan VALUES ('C00001', 'P00002', '1-Oct-2014')
INSERT INTO #CustomerPlan VALUES ('C00001', 'P00003', '10-Oct-2014')
INSERT INTO #CustomerPlan VALUES ('C00001', 'P00004', '25-Oct-2014')
INSERT INTO #CustomerPlan VALUES ('C00002', 'P00001', '1-Oct-2014')
INSERT INTO #CustomerPlan VALUES ('C00002', 'P00002', '1-Nov-2014')

SELECT * FROM #CustomerPlan
--------------------------------------------------------
;WITH   CTE as
(
        SELECT RN = ROW_NUMBER() OVER (PARTITION BY   CustomerCode ORDER BY   StartDate ASC),   *
        FROM #CustomerPlan
)
SELECT
        [Current Row].*,
        ISNULL(DATEADD(DAY, -1, [Next Row].StartDate), '31-Dec-2099') AS EndDate
FROM   CTE [Current Row]
        LEFT JOIN CTE [Next Row] ON [Current Row].CustomerCode   = [Next Row].CustomerCode   AND  [Next Row].RN   = [Current Row].RN   + 1
ORDER BY [Current Row].CustomerCode, [Current Row].RN;

--------------------------------------------------------
SELECT *,
DATEADD(DAY, -1, LEAD(StartDate, 1,'01-Jan-2100')
        OVER (PARTITION BY   CustomerCode ORDER BY   StartDate ASC))   AS EndDate
FROM   #CustomerPlan
--------------------------------------------------------
SELECT   CustomerCode, PlanCode AS CurrentPlanCode,
LAG(PlanCode, 1, 'NA')
        OVER (PARTITION BY   CustomerCode ORDER BY   StartDate ASC)   AS LastPlan
FROM   #CustomerPlan;
--------------------------------------------------------
SELECT   CustomerCode, PlanCode AS CurrentPlanCode,
LAG(PlanCode, 2, 'NA')
        OVER (PARTITION BY   CustomerCode ORDER BY   StartDate ASC)   AS LastPlan
FROM   #CustomerPlan;
--------------------------------------------------------