SQL Function for On Balance Volume (Financial Query)

Question

I would like to create a function for On Balance Volume (SQL Function). This is too complex of a calculation for met to figure out but here is the outline of the User Defined Table Function. If someone could help me to fill in the blanks I would appreciate it. Craig

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

    DECLARE @rowcount SMALLINT
     INSERT @results ([date], [obv])

// CREATE A FUNCTION FOR ON BALANCE VOLUME
// On Balance Volume is the Summ of Volume for Total Periods
// OBV = 1000 at Period = 0
// OBV = OBV Previous + Previous Volume if Close > Previous Close
// OBV = OBV Previous - Previous Volume if Close < Previous Close
//  OBV = OBV Previous if Close = Previous Close

//  The actual Value of OBV is not important so to keep the ratio low we reduce the 
// Total Value of Tickers by 1/10th or 1/100th
// For Value of Volume = Volume * .01 if Volume < 999
// For Value of Volume = Volume * .001 If Volume >= 999
    FROM Tickers

   RETURN

    END

This is the Tickers 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
 ) 

Here is an example of the data

   ticker  date  close  volume 
   pzi:  5-10-10  10.94    805 
   pzi;  5-11-10  11.06    444 
   pzi:  5-12-10  11.42    236 
   pzi:  5-13-10  11.3    635 
   pzi:  5-14-10  11    316 

   date  obv 
   5-10  996.38 
   5-11  996.82 
   5-12  997.06 
   5-13  996.42 
   5-14  996.11 

Answer 1

Here's a working inline table valued function (most efficient for the optimizer):

CREATE FUNCTION [dbo].[GetStdDev3] (@TKR VARCHAR(10))
    RETURNS TABLE
    AS RETURN (
    WITH    Y AS ( SELECT   *
                   ,OBV_Change = ISNULL(SIGN(currclose - prevclose)
                                        * volume, 1000)
           FROM     ( SELECT    curr.date
                               ,curr.[CLOSE] AS currclose
                               ,prev.[CLOSE] AS prevclose
                               ,curr.volume
                      FROM      Tickers AS curr
                      LEFT JOIN Tickers AS prev
                                ON prev.ticker = @TKR
                                   AND prev.date = ( SELECT MAX(date)
                                                     FROM   Tickers
                                                     WHERE  ticker = @TKR
                                                            AND date < curr.date
                                                   )
                      WHERE     curr.ticker = @TKR
                    ) AS X
         )
SELECT  y1.date
       ,SUM(y2.OBV_Change) AS OBV
       ,ROW_NUMBER() OVER(ORDER BY y1.date) AS dayno
FROM    Y AS y1
LEFT JOIN Y AS y2
        ON y2.date <= y1.date
GROUP BY y1.date
)

I wasn't sure about the normalization - I left that out - adding it in might need you to make this in to a multi-statement TVF.