none
SQL Procedure - Use a Cursor?

    Question

  • Hi,

     

    I have the following table which I need to create a stored procedure for. 

    I need to calculate the Gain, Loss, AverageGain, AverageLoss and RSI values.

     

    They are calculated as follows:

    Gain = (Close - Open) NB: If gain is negative then 0 should be stored.

    Loss = (Open - Close) NB: Loss should be stored as a positive value.  If it is negative then 0 should be stored.

     

    AverageGain:

    - The records should be ordered in ascending date order. 

    - The first 14 values should not contain a AverageGain value. 

    - The fifthteen record should be the 'total of the previous 14 records Gain'/14.

    - For all other records (16+) the AverageGain should be calculated as '((previous records AverageGain*13)+Current Records Gain)/14'

    AvergageLoss: Same as gain but for loss.

     

    RSI = 100-(100/(1+AverageGain))

     

    I am unsure of the best way to achieve this.  Is there an SQL query that can do this or do  have to use a cursor?

     

    Thanks in advance,

    Ben.

     

    USE [StockData]

    GO

    /****** Object:  Table [dbo].[CompanyDailyData]    Script Date: 09/14/2008 01:46:28 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[CompanyDailyData](

                [Id] [int] IDENTITY(1,1) NOT NULL,

                [CompanyId] [int] NOT NULL,

                [Date] [datetime] NOT NULL,

                [Open] [decimal](18, 5) NOT NULL,

                [High] [decimal](18, 5) NOT NULL,

                [Low] [decimal](18, 5) NOT NULL,

                [Close] [decimal](18, 5) NOT NULL,

                [Volume] [bigint] NOT NULL,

                [Gain] [decimal](18, 5) NULL,

                [Loss] [decimal](18, 5) NULL,

                [AverageGain] [decimal](18, 5) NULL,

                [AverageLoss] [decimal](18, 5) NULL,

                [RSI] [decimal](3, 2) NULL,

     CONSTRAINT [PK_CompanyDailyData] PRIMARY KEY CLUSTERED

    (

                [Id] ASC

    )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    ) ON [PRIMARY]

     

     

    Sunday, September 14, 2008 1:16 AM

Answers

  • Hi,

     

    A cursor based solution can be found here: http://knol.google.com/k/linus-nilsson/relative-strength-index-rsi-in-tsql/g1yluc4toyn/18

    Cursor may be a  better alternative if this requires looping over a large set of data, since it would create a large amount of inner joins.

    Wednesday, November 16, 2011 1:20 AM
  • >> have the following table which I need to create a stored procedure for. 
    I need to calculate the gain_amt, loss_amt, gain_amt_avg, loss_amt_avg and rsi values. <<
    Well in COBOL or a gametic tape file. In SQL we use virtual tables and columns instead. 
    Thanks for the attempt at DDL, but it is all wrong – data types, data element names etc. are awful. Why did you use the count of physical insertion attempts in this table as a key? We use the DUNS for a company identifier, not some vague local integer. Did you want to use a ticker symbol? Can you give me an example of a “something_volume” that needs a BIGINT? That is waaaay more than the atoms in the Universe! If you keep inviting bad data like this, you will get it. DATE is a reserved word, etc. 
    >> They are calculated as follows:
    Gain = (Close - Open) NB: If gain is negative then 0 should be stored.
    Loss = (Open - Close) NB: Loss should be stored as a positive value. If it is negative then 0 should be stored. <<
    Thanks for the attempt at DDL, but it is all wrong – data types, data element names etc. are awful. Why did you use the count of physical insertion attempts in this table as a key?  Did you want to use a ticker symbol? We use the DUNS for a company identifier, not some vague local integer. Can you give me an example of a “something_volume” that needs a BIGINT? That is waaaay more than the atoms in the Universe! If you keep inviting bad data like this, you will get it. DATE is a reserved word, etc. 
    >> 
    AverageGain:
    - The records [sic] should be ordered in ascending date order. 
    - The first 14 values should not contain a AverageGain value. 
    - The fifteenth record [sic] should be the 'total of the previous 14 records [sic]  Gain'/14.
    - For all other records [sic]  (16+) the AverageGain should be calculated as '((previous records [sic]  AverageGain*13) + Current records [sic] Gain)/14'
    AvergageLoss: Same as gain but for loss.
     
    rsi = 100.0-(100.09/(1.00 + gain_amt_avg) <<
    Why do you have both gain_amt and loss_amt? The goal of all DB is removal redundancy, not to increase it. This the daily_price_delta, but if you want to be a bad data modeler use the formula:
    CASE WHEN (a-b) < 0.00 THEN 0.00 ELSE (a-b) END
    As an exercise, write this using ABS(), and SIGN(); it will be faster but a bitch to maintain. 
    Rows are not records [sic]; this is fundamental. 
    Why are you putting a set of computed values in a base table? Try this: 
    CREATE TABLE Company_Daily_Stock_Prices
    (duns CHAR(9) NOT NULL,
     trade_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
     PRIMARY KEY (duns, trade_date)),
     opening_price DECIMAL (18,5) NOT NULL,
     daily_high_price DECIMAL (18,5) NOT NULL,
     daily_low_price DECIMAL (18,5) NOT NULL,
     closing_price DECIMAL (18,5) NOT NULL,
     trade_volume INTEGER NOT NULL,
      daily_price_delta COMPUTED AS (opening_price – closing_price));
    Now you are in trouble unless you have the most current version of SQL Server, 2012. You need the  [RANGE | ROW] option in the OVER clause to really do this. Or get DB2 that has had it for almost a decade:
    SELECT AVG (CASE WHEN daily_price_delta >= 0.00 THEN daily_price_delta END) 
           OVER (PARTITION BY duns 
                     ORDER BY trade_date 
                  RANGE BETWEEN 14 PRECEDING AND CURRENT ROW)
           AS gain_amt_avg
      FROM Company_Daily_Stock_Prices;
    Do the same for the loss_amt_avg, and rsi.  If you do nothve this, then we can do a table driven versionof it. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Wednesday, November 16, 2011 5:25 AM
  • In fact you just want to have some subtotals over 14-day windows.

    So in fact, your main problem is to assign the different dates to their respective time window.

    The rest should be straightforward.

    Taking a simplified version of your table

    CREATE TABLE [dbo].[CompanyDailyData](
                [Id] [int] IDENTITY(1,1) NOT NULL,                 
       [CompanyID] [int] NOT NULL,          
       [TransactionDate] [datetime] NOT NULL,
                [Gain] [decimal](18, 5) NULL
    ) ON [PRIMARY]

     

    You can do this, this way.

     DECLARE @start_date AS DATE
     SET @start_date = '2011-10-17'

    ;WITH CTE AS (
     SELECT
       CompanyID
       , DATEDIFF(DAY, @start_date, TransactionDate) / 14 AS TimeWindow
       , TransactionDate
       , Gain
     FROM dbo.CompanyDailyData

    )
    SELECT
     CompanyID
     , TimeWindow
     , TransactionDate
     , SUM(Gain) AS TotalGain
    FROM CTE
    GROUP BY CompanyID
     , ROLLUP(TimeWindow, TransactionDate)

    The ROLLUP operator will allow you to calculate the subtotals. The operator is available from SQL Server 2008 onwards.

     

    Not certain why you want to have the subtotals to appear in a separate column, but if you really want to do so, this should not be to complicated.


    • Edited by Laurent Couartou Thursday, November 17, 2011 6:09 AM query modified to make use of the ROLLUP operator.
    • Proposed as answer by Laurent Couartou Thursday, November 17, 2011 6:10 AM
    • Marked as answer by Kalman TothModerator Sunday, October 21, 2012 7:13 PM
    Wednesday, November 16, 2011 3:25 PM

All replies

  • If you are concerned about performance, you should not use cursor.   You can do anything you can do with cursor with set-based operations.

    To ease your way into set-based operations, use #temptables to proceed step-by-step toward the final result goal.

    The following is an example for set-based operations using a #temptable:

    USE AdventureWorks2008; 
    
    SELECT SalesOrderID, 
           Quantity=OrderQty, 
           UnitPrice, 
           LineTotal =CONVERT(money, 0.0) 
    INTO   #Pricing 
    FROM   Sales.SalesOrderDetail 
    
    UPDATE #Pricing 
    SET    LineTotal = Quantity * UnitPrice 
    
    SELECT SalesOrderID, 
           Total = sum(LineTotal) 
    FROM   #Pricing 
    GROUP  BY SalesOrderID 
    ORDER  BY SalesOrderID  

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Sunday, September 14, 2008 1:49 AM
    Moderator
  • Hi,

     

    A cursor based solution can be found here: http://knol.google.com/k/linus-nilsson/relative-strength-index-rsi-in-tsql/g1yluc4toyn/18

    Cursor may be a  better alternative if this requires looping over a large set of data, since it would create a large amount of inner joins.

    Wednesday, November 16, 2011 1:20 AM
  • >> have the following table which I need to create a stored procedure for. 
    I need to calculate the gain_amt, loss_amt, gain_amt_avg, loss_amt_avg and rsi values. <<
    Well in COBOL or a gametic tape file. In SQL we use virtual tables and columns instead. 
    Thanks for the attempt at DDL, but it is all wrong – data types, data element names etc. are awful. Why did you use the count of physical insertion attempts in this table as a key? We use the DUNS for a company identifier, not some vague local integer. Did you want to use a ticker symbol? Can you give me an example of a “something_volume” that needs a BIGINT? That is waaaay more than the atoms in the Universe! If you keep inviting bad data like this, you will get it. DATE is a reserved word, etc. 
    >> They are calculated as follows:
    Gain = (Close - Open) NB: If gain is negative then 0 should be stored.
    Loss = (Open - Close) NB: Loss should be stored as a positive value. If it is negative then 0 should be stored. <<
    Thanks for the attempt at DDL, but it is all wrong – data types, data element names etc. are awful. Why did you use the count of physical insertion attempts in this table as a key?  Did you want to use a ticker symbol? We use the DUNS for a company identifier, not some vague local integer. Can you give me an example of a “something_volume” that needs a BIGINT? That is waaaay more than the atoms in the Universe! If you keep inviting bad data like this, you will get it. DATE is a reserved word, etc. 
    >> 
    AverageGain:
    - The records [sic] should be ordered in ascending date order. 
    - The first 14 values should not contain a AverageGain value. 
    - The fifteenth record [sic] should be the 'total of the previous 14 records [sic]  Gain'/14.
    - For all other records [sic]  (16+) the AverageGain should be calculated as '((previous records [sic]  AverageGain*13) + Current records [sic] Gain)/14'
    AvergageLoss: Same as gain but for loss.
     
    rsi = 100.0-(100.09/(1.00 + gain_amt_avg) <<
    Why do you have both gain_amt and loss_amt? The goal of all DB is removal redundancy, not to increase it. This the daily_price_delta, but if you want to be a bad data modeler use the formula:
    CASE WHEN (a-b) < 0.00 THEN 0.00 ELSE (a-b) END
    As an exercise, write this using ABS(), and SIGN(); it will be faster but a bitch to maintain. 
    Rows are not records [sic]; this is fundamental. 
    Why are you putting a set of computed values in a base table? Try this: 
    CREATE TABLE Company_Daily_Stock_Prices
    (duns CHAR(9) NOT NULL,
     trade_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
     PRIMARY KEY (duns, trade_date)),
     opening_price DECIMAL (18,5) NOT NULL,
     daily_high_price DECIMAL (18,5) NOT NULL,
     daily_low_price DECIMAL (18,5) NOT NULL,
     closing_price DECIMAL (18,5) NOT NULL,
     trade_volume INTEGER NOT NULL,
      daily_price_delta COMPUTED AS (opening_price – closing_price));
    Now you are in trouble unless you have the most current version of SQL Server, 2012. You need the  [RANGE | ROW] option in the OVER clause to really do this. Or get DB2 that has had it for almost a decade:
    SELECT AVG (CASE WHEN daily_price_delta >= 0.00 THEN daily_price_delta END) 
           OVER (PARTITION BY duns 
                     ORDER BY trade_date 
                  RANGE BETWEEN 14 PRECEDING AND CURRENT ROW)
           AS gain_amt_avg
      FROM Company_Daily_Stock_Prices;
    Do the same for the loss_amt_avg, and rsi.  If you do nothve this, then we can do a table driven versionof it. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
    Wednesday, November 16, 2011 5:25 AM
  • In fact you just want to have some subtotals over 14-day windows.

    So in fact, your main problem is to assign the different dates to their respective time window.

    The rest should be straightforward.

    Taking a simplified version of your table

    CREATE TABLE [dbo].[CompanyDailyData](
                [Id] [int] IDENTITY(1,1) NOT NULL,                 
       [CompanyID] [int] NOT NULL,          
       [TransactionDate] [datetime] NOT NULL,
                [Gain] [decimal](18, 5) NULL
    ) ON [PRIMARY]

     

    You can do this, this way.

     DECLARE @start_date AS DATE
     SET @start_date = '2011-10-17'

    ;WITH CTE AS (
     SELECT
       CompanyID
       , DATEDIFF(DAY, @start_date, TransactionDate) / 14 AS TimeWindow
       , TransactionDate
       , Gain
     FROM dbo.CompanyDailyData

    )
    SELECT
     CompanyID
     , TimeWindow
     , TransactionDate
     , SUM(Gain) AS TotalGain
    FROM CTE
    GROUP BY CompanyID
     , ROLLUP(TimeWindow, TransactionDate)

    The ROLLUP operator will allow you to calculate the subtotals. The operator is available from SQL Server 2008 onwards.

     

    Not certain why you want to have the subtotals to appear in a separate column, but if you really want to do so, this should not be to complicated.


    • Edited by Laurent Couartou Thursday, November 17, 2011 6:09 AM query modified to make use of the ROLLUP operator.
    • Proposed as answer by Laurent Couartou Thursday, November 17, 2011 6:10 AM
    • Marked as answer by Kalman TothModerator Sunday, October 21, 2012 7:13 PM
    Wednesday, November 16, 2011 3:25 PM