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.
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
