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.TICKER = @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