Query to compute the difference between a SUM and previous SUM, GROUP BY discontinuous dates

Question

I have the following query that gives the daily total value of a portfolio.

SELECT RecDate, Sum(Mval) as TotalVal
FROM DailyVal
WHERE RecDate >= DateValue("1/1/2013")
GROUP BY RecDate;

It works great and gives result as the following:

RecDate TotalVal
1/10/2013   4465
1/11/2013   4471
1/14/2013   4472
1/15/2013   4477

However I'd like to compute the difference between say 1/11/2013 and 1/10/2013 and so on for every row.

I usually do an INNER JOIN on the table with an alias ON the ID - 1 and perform the computation as desired. Unfortunately there isn't an index that makes sense when aggregating by GROUP BY.

My question would be is there actually a way to give an index to the query so it could look like:

ID  RecDate TotalVal
1  1/10/2013    4465
2  1/11/2013    4471
3  1/14/2013    4472
4  1/15/2013    4477

That way I can take my normal approach.

Otherwise, is there a more efficient way to perform this task?

Answer 1

You should be able to do this with a sub-query and a self-join:

SELECT 
    D1.RecDate, 
    TotalVal, 
    TotalVal - NZ(TotalVal2,0) Delta
FROM (
    SELECT RecDate, Sum(Mval) as TotalVal
    FROM DailyVal
    WHERE RecDate >= DateValue("1/1/2013")
    GROUP BY RecDate) D1
LEFT JOIN (
    SELECT RecDate, Sum(Mval) as TotalVal2
    FROM DailyVal
    WHERE RecDate >= DateValue("1/1/2013")
    GROUP BY RecDate) D2
ON D2.RecDate = (SELECT MAX(RecDate) FROM DailyVal WHERE RecDate < D1.RecDate)

;