Problem with SQL In Line Table Function... Query

Hi I have the following query running a function to get the Standard Deviation for a set of Tickers in the following table...

 GO
CREATE TABLE [dbo].[Tickers](
[ticker] [varchar](10) NULL,
[date] [datetime] NULL,
[high] [float] NULL,
[low] [float] NULL,
[open] [float] NULL,
[close] [float] NULL,
[volume] [float] NULL,
[time] [datetime] NULL,
[change] [float] NULL
) ON [PRIMARY]

THE PROBLEM: THE FOLLOWING IN LINE TABLE FUNCTION RETURNS STDDEV CALC which is in turn used by a SPROC To calculate Bollinger bands ... (mov average + 2 * STDEV) etc...

The results that I get for some Tickers has weird data ... this is the result set for the ticker 'ATE' or just a sample of the result set.

dayno   ticker                  stddev
484    11/13/2009              0.544772123613694         
485    11/16/2009              0.323959874058724          
486    11/17/2009              0.287909129182731         
487    11/18/2009              0.225018517756637         
488    11/19/2009              4.94974746848848E-02      
489    11/20/2009              4.94974746848848E-02    

As you can see the last two values for some of the tickers results in 'weird data' and the actual ticker table is within very normal ranges.

As you can see from the following in line table function there was some funny stuff going on at the end because it is using a 20 day period and the last value always came back as NULL, so I asked experts to adjust and this is what Peter came up with... it usually works find but as you can see above sometimes does not - does anyone have a suggestion on how I may fix this dilemma??

ALTER FUNCTION dbo.GetStdDev3 (@TKR VARCHAR(10))
RETURNS @results TABLE (
dayno   SMALLINT IDENTITY(1,1) PRIMARY KEY
, [date]  DATETIME
, [stddev] 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],[stddev])
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.TICKE开发者_StackOverflow社区R = @TKR 
  AND y.TICKER = @TKR 
GROUP BY x.[DATE]

SET @rowcount = @@ROWCOUNT

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

4.94974746848848E-02 is actually the same thing as 0.0494974746848848

Are you sure this is in error? Seems to me it could just be a low deviation.

Ditto what "d." said. The standard deviation for the last two dates is low, but it was decreasing over time anyway. Also, all that the last update statement does is to set the last row (latest date) in the set equal to the second-to-last value. (Perhaps "adjsut" might have been "delete"?)