Time-Series calculations using SQL Server 2008: the CROSS APPLY method

For these examples I will use RSI calculations, which require a chronologically ordered set of data where:

RSI = 100 - ( 100 / (1 + (MA(U) / MA(D)) ) )

MA: simple or exponential moving average, depending on the kind of RSI we are interested in.
U: set of current close price - previous close price, if current close price > previous close price (0 otherwise), for each period.
D: set of previous close price - current close price, if current close price < previous close price (0 otherwise), for each period.

This method uses CROSS APPLY, to avoid iterative or cursor-based processes.

First, I create a user-defined table type called OrderedPriceSet:


CREATE TYPE dbo.OrderedPriceSet AS TABLE
(
  ValueRank INT PRIMARY KEY,
  ValuePrice SMALLMONEY
);

And then I wrote a table-valued user-defined function that calculates the RSI values for each row in a given table of close prices:


CREATE FUNCTION Utility.udfCutlerRSI
( 
  @priceSet OrderedPriceSet READONLY,
  @numValues INT 
)
RETURNS @RSI_table TABLE
(
  ValueRank INT PRIMARY KEY,
  ValuePrice SMALLMONEY
)
AS 
BEGIN
  -- using CROSS APPLY

  -- first, generate the ordered set of differentials (close(now) - close(yesterday))
  DECLARE @differentials As OrderedPriceSet;


  INSERT @differentials (ValuePrice, ValueRank)
  SELECT apply_ps.ValuePrice, ps.ValueRank FROM @priceSet As ps
  CROSS APPLY ( SELECT (ps.ValuePrice - inner_ps.ValuePrice) As ValuePrice
FROM @priceSet As inner_ps WHERE inner_ps.ValueRank = ps.ValueRank - 1 ) As apply_ps;


  -- now we need to generate the U's and D's of each @numValues period, and calculate the RSI (can be done in one shot I think)
  INSERT @RSI_table (ValueRank, ValuePrice)
  SELECT diffs.ValueRank,
 CASE sub_calcs.Dsum
   WHEN 0 THEN 100.0
   ELSE (100.0 - (100.0 / (1.0 + ((sub_calcs.Usum / CONVERT(FLOAT, @numValues)) / (sub_calcs.Dsum / CONVERT(FLOAT, @numValues) ))) )) 
  END AS ValuePrice
  FROM @differentials As diffs
  CROSS APPLY ( SELECT inner_diffs.ValueRank,
SUM(CASE
WHEN last_vals.ValuePrice > 0 THEN last_vals.ValuePrice
ELSE 0
END) As Usum,
SUM(CASE
WHEN last_vals.ValuePrice < 0 THEN ABS(last_vals.ValuePrice)
ELSE 0
END) As Dsum
  FROM @differentials As inner_diffs
  CROSS APPLY ( SELECT TOP(@numValues) ValuePrice FROM @differentials
                WHERE ValueRank <= inner_diffs.ValueRank
ORDER BY ValueRank DESC ) last_vals
  WHERE inner_diffs.ValueRank = diffs.ValueRank
  GROUP BY ValueRank) sub_calcs;


  RETURN;

END;

Finally, I use this UDF by passing it an OrderedPriceSet of chronologically ranked Close Price values, and a number of periods for my RSI, and INSERT the output into a table variable which now contains all RSIs for each ranked row.