Lead() and LAG() functionality in SQL Server 2008

Question

Hope all the SQL GURUS out there are doing great :)

I am trying to simulate LEAD() and LAG() functionality in SQL Server 2008.

This is my scenario: I have a temp table which is populated using the base query with the business logic for mileage. I want to calculate accumulated mileage for each user per day.

The temp table is setup using ROW_NUMBER(), so I have all the data needed in the temp table except the accumulated mileage.

I have tried using a CTE with the base query and self joining with itself and couldn't get it working. I am attaching the screen shot for the same.

Any help/suggestion would be appreciated.

Answer 1

You are on the right track by joining the table to itself. I included 2 methods of doing this below that should work fine here. The first trick is in your ROW_NUMBER, be sure to partition by the user id and sort by the date. Then you can use either an INNER JOIN with aggregation or CROSS APPLY to build your running totals.

Setting up the data with the partitioned ROW_NUMBER():

DECLARE @Data TABLE (
    RowNum INT,
    UserId INT,
    Date DATE,
    Miles INT
)
INSERT @Data 
    SELECT
        ROW_NUMBER() OVER (PARTITION BY UserId
            ORDER BY Date) AS RowNum,
        *
    FROM (
        SELECT 1, '2015-01-01', 5
        UNION ALL SELECT 1, '2015-01-02', 6
        UNION ALL SELECT 2, '2015-01-01', 7
        UNION ALL SELECT 2, '2015-01-02', 3
        UNION ALL SELECT 2, '2015-01-03', 2
        ) T (UserId, Date, Miles)

Use INNER JOIN with Aggregation

SELECT
    D1.UserId,
    D1.Date,
    D1.Miles,
    SUM(D2.Miles) AS [Total]
FROM @Data D1
    INNER JOIN @Data D2
        ON D1.UserId = D2.UserId
            AND D2.RowNum <= D1.RowNum
GROUP BY
    D1.UserId,
    D1.Date,
    D1.Miles

Use CROSS APPLY for the running total

SELECT
    UserId,
    Date,
    Miles,
    Total
FROM @Data D1
    CROSS APPLY (
        SELECT SUM(Miles) AS Total
        FROM @Data
        WHERE UserId = D1.UserId
            AND RowNum <= D1.RowNum
    ) RunningTotal

Output is the same for each method:

UserId      Date       Miles       Total
----------- ---------- ----------- -----------
1           2015-01-01 5           5
1           2015-01-02 6           11
2           2015-01-01 7           7
2           2015-01-02 3           10
2           2015-01-03 2           12