Calculate arithmetic return from a table of values

Question

I've created a table of index price levels (eg, S&P 500) that I'd like to calculate the daily return of. Table structure looks like this:

Date        Value
2009-07-02  880.167341
2009-07-03  882.235134
2009-07-06  881.338052
2009-07-07  863.731494
2009-07-08  862.458985

I'd like to calculate the daily arithmetic return (ie, percentage return) of the index, defined as:

Daily Return = P(2)/P(1) - 1

Where P represents the index value in this case. Given the input table presented above, the desired output would look like this:

    Date        Return
    2009-07-03  0.002349318
    2009-07-06  -0.001016829
    2009-07-07  -0.019977077
    2009-07-08  -0.001473269

It occurs to me that a self join would work, but I'm not sure of the best way to increment the date on the second table to account for weekends.

Any thoughts on the best way to go about this?

Answer 1

WITH cteRank AS (
    SELECT [Date], Value, 
           ROW_NUMBER() OVER(ORDER BY [Date]) AS RowNum
        FROM YourTable
)
SELECT c1.[Date], c1.Value/c2.Value - 1 as [Return]
    from cteRank c1
        inner join cteRank c2
            on c1.RowNum - 1 = c2.RowNum
    where c1.RowNum > 1

Answer 2

A simple CROSS APPLY

SELECT
  Tlater.Date, (Tlater.Value / TPrev2.Value) - 1
FROM
   MyTable Tlater
   CROSS APPLY
   (
    SELECT TOP 1 TPrev.Value 
    FROM MyTable TPrev
    WHERE TPrev.Date < Tlater.Date
    ORDER BY TPrev.Date
   ) TPrev2

Note: this becomes trivial in Denali (SQL Server 2012) with LAG (untested, may need a CTE)

SELECT 
    OrderDate, 
    (Value / (LAG(Value) OVER (ORDER BY Date))) -1
FROM
    MyTable

Or

;WITH cPairs AS
(
   SELECT 
      Date, 
      Value AS Curr,
      LAG(Value) OVER (ORDER BY Date) AS Prev
   FROM
    MyTable
)
SELECT 
    Date, 
    (Curr / Prev) -1
FROM
    cPairs