GET ISNULL from COMPLEX FUNCTION

Question

I have a SQL Table UDF That gets a Standard Deviation from a 20 day moving average of values... The table its computing from is: Tickers([date] datetime, [close] numeric(7,2))

The Function computes a Table GetStDev([date] datetime, stddev numeric(7,2).

The last row of the stddev column is always NULL (due to STDEV calc?)... I need to replace that NULL value in the last row with the value from the previous row ([date] -1)... but in do so doing, do I have calculate the whole query twice and select TOP 1 stddev from xxx, and even so I am not sure how to write such a long SQL statement... Here is my In Line Table UDF:

ALTER FUNCTION GetStdDev 
    (
      @TKR  VARCHAR(10)
    )
RETURNS TABLE
AS
    RETURN
    (
     SELECT x.[date], ISNULL(STDEV(y.[Close]),0) stdev
     FROM Tickers x, Tickers y
     WHERE x.[DATE] > (SELECT TOP 1 z.[DATE] FROM TICKERS z WHERE z.TICKER = @TKR ORDER BY z.DATE ASC)+20
     AND (DATEDIFF(day, x.[date], GETDATE()) <= 730) 
     AND x.TICKER = @TKR AND y.TICKER = @TKR
     AND x.[DATE] BETWEEN y.[DATE]-20 AND y.[DATE]
     GROUP BY x.DATE
    )

Answer 1

First, in your WHERE clause, you should replace this:

-- reformatted for readability
WHERE x.[DATE] > (
          SELECT TOP 1 z.[DATE] FROM TICKERS z 
          WHERE z.TICKER = @TKR ORDER BY z.DATE ASC
          )+20
  AND (DATEDIFF(day, x.[date], GETDATE()) <= 730)

With this:

WHERE x.[DATE] > (
          SELECT DATEADD(DAY,20,MIN(z.[DATE])) 
          FROM TICKERS z WHERE z.TICKER = @TKR
          )
  AND x.[DATE] > DATEADD(DAY,-731,GETDATE())

Unless you tested your version and found it to be faster.

Beyond that, you could replace this with a multi-statement table-valued function. eg:

CREATE FUNCTION dbo.GetStdDev (@TKR VARCHAR(10))
RETURNS @results TABLE (
  dayno   SMALLINT IDENTITY(1,1) PRIMARY KEY
, [date]  DATETIME
, [stdev] FLOAT
) 
AS BEGIN

  DECLARE @min_sysdate DATETIME, @min_tkrdate DATETIME, @rowcount SMALLINT

  SET @min_sysdate = DATEADD(DAY,-731,GETDATE())
  SET @min_tkrdate = DATEADD(DAY,20,(
    SELECT MIN(DATE) FROM TICKERS WHERE TICKER = @TKR))

  INSERT @results ([date],[stdev])
  SELECT x.[date], ISNULL(STDEV(y.[Close]),0) AS stdev
  FROM Tickers x
  JOIN Tickers y ON x.[DATE] BETWEEN DATEADD(DAY,-20,y.[DATE]) AND y.[DATE]
  WHERE x.[DATE] > @min_tkrdate 
    AND x.[DATE] > @min_sysdate
    AND x.TICKER = @TKR 
    AND y.TICKER = @TKR 
  GROUP BY x.[DATE]

  SET @rowcount = @@ROWCOUNT

  UPDATE @results SET [stdev] = (
    SELECT [stdev] FROM @results WHERE dayno = @rowcount-1)
  WHERE dayno = @rowcount

  RETURN

END