LAG/LEAD equivalent with grouping (SQL Server 2008

Question

Note: I am using SQL Server 2008 R2 and built in LEAD/LAG functions are not available.

I need to update a table's column to contain the 'previous' and 'next' values for ProductID - the table needs to store PrevProductID (LAG), ProductID and NextProductID (LEAD). The code below does this very nicely and was adapted from Geri Reshef's answer to http://blog.sqlauthority.com/2011/11/24/sql-server-solution-to-puzzle-simulate-lead-and-lag-without-using-sql-server-2012-analytic-function/

USE AdventureWorks2008R2
GO
WITH T1 AS
(   SELECT Row_Number() OVER(ORDER BY SalesOrderID,ProductID) N,
        s.SalesOrderID,
        s.ProductID
    FROM Sales.SalesOrderDetail s
    WHERE SalesOrderID IN (43670, 43667, 43663)
)
SELECT SalesOrderID,
    CASE WHEN N%2=1 
    THEN MAX(CASE WHEN N%2=0 
            THEN ProductID 
        END) OVER (Partition BY N/2) 
        ELSE MAX(CASE WHEN N%2=1 
            THEN ProductID 
        END) OVER (Partition BY (N+1)/2) 
    END PrevProductID,
ProductID,
CASE WHEN N%2=1 
    THEN MAX(CASE WHEN N%2=0 
            THEN ProductID 
        END) OVER (Partition BY (N+1)/2) 
        ELSE MAX(CASE 
            WHEN N%2=1 THEN ProductID 
        END) OVER (Partition BY N/2) 
    END NextProductID
FROM T1
ORDER BY 
    t1.SalesOrderID,
    t1.ProductID

These are the results and the Lag/Lead are applied to all rows.

SalesOrderID PrevProductID ProductID   NextProductID
------------ ------------- ----------- -------------
43663        NULL          760         710
43667        760           710         773
43667        710           773         775
43667        773           775         778
43667        775           778         709
43670        778           709         710
43670        709           710         773
43670        710           773         776
43670        773           776         NULL

What I need to do is to group the Lag/Lead values by SalesOrderID with the first occurrence pointing to Lag of NULL and the last occurrence pointing to Lead of NULL within a SalesOrderID group.

SalesOrderID PrevProductID ProductID   NextProductID
------------ ------------- ----------- -------------
43663        NULL          760         NULL
43667        NULL          710         773
43667        710           773         775
43667        773           775         778
43667        775           778         NULL
43670        NULL          709         710
43670        709           710         773
43670        710           773         776
43670        773           776         NULL

Answer 1

You can do this by simply adding SalesOrderID into each of the PARTITION BY clauses in the query (ROW_NUMBER() OVER(), then 4 x MAX() OVER()):

WITH T1 AS
(   SELECT  SalesOrderID, 
            ProductID, 
            N = ROW_NUMBER() OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderID, ProductID)
    FROM    (VALUES 
                (43663, 760), (43667, 710), (43667, 773), (43667, 775), (43667, 778), 
                (43670, 709), (43670, 710), (43670, 773), (43670,  776),
                (43680,  1), (43680,  2), (43680,  3), (43680,  4), (43680,  5),
                (43680,  6), (43680,  7), (43680,  8), (43680,  9), (43680,  10),
                (43681,  1), (43681,  2), (43681,  3), (43681,  4), (43681,  5),
                (43681,  6), (43681,  7), (43681,  8), (43681,  9), (43681,  10)
            ) x (SalesOrderID, ProductID)
)
SELECT  T1.SalesOrderID,
        PrevProductID = CASE WHEN N % 2 = 1
                            THEN MAX(CASE WHEN N % 2 = 0 THEN T1.ProductID END)
                                    OVER(PARTITION BY T1.SalesOrderID, N / 2)
                            ELSE 
                                MAX(CASE WHEN N % 2 = 1 THEN ProductID END)
                                    OVER(PARTITION BY T1.SalesOrderID, (N + 1) / 2)
                        END,
        T1.ProductID,
        NextProductID = CASE WHEN N % 2 = 1
                            THEN MAX(CASE WHEN N % 2 = 0 THEN T1.ProductID END)
                                    OVER(PARTITION BY T1.SalesOrderID, (N + 1) / 2)
                            ELSE 
                                MAX(CASE WHEN N % 2 = 1 THEN ProductID END)
                                    OVER(PARTITION BY T1.SalesOrderID, N / 2)
                        END
FROM    T1;

Which (excluding the additional rows I have added for further testing) yields:

SalesOrderID    PrevProductID   ProductID   NextProductID
---------------------------------------------------------------
43663           NULL            760             NULL
43667           NULL            710             773
43667           710             773             775
43667           773             775             778
43667           775             778             NULL
43670           NULL            709             710
43670           709             710             773
43670           710             773             776
43670           773             776             NULL