How to calculate the Rate Of Change (ROC)

  • alter

    proc Roc as

    IF

    OBJECT_ID(N'tempdb..#FinalROC', N'U') IS NOT NULL drop

    table #FinalROC; create

    table #FinalROC (

    Transaction_Id int, Transaction_Date

    datetime, Symbol_Code

    varchar(50), Symbol_Name

    varchar(50), Close_Price

    decimal(18,2), ROC

    decimal(18,2), ROC_Flag

    varchar(50)) insert

    into #FinalROC( Transaction_Id

    , Transaction_Date

    , Symbol_Code

    , Symbol_Name

    , Close_Price

    ) (

    select Transaction_Id

    , Transaction_Date

    , Symbol_Code

    , Symbol_Name

    , Close_Price

    from

    EOD_NSE_Stock Where

    (Series_Code='EQ' or Series_Code='BE') and

    Symbol_Code is not null and Transaction_Date

    =CONVERT(VARCHAR(10), GETDATE(), 101))

    Declare @MaxTransId int , @MinTransId int Declare

    @SC varchar(50) set

    @MinTransId=(select Min(Transaction_Id) from #FinalROC

    ) set

    @MaxTransId=(select Max(Transaction_Id) from #FinalROC

    )

    while (@MinTransId<@MaxTransId)

    begin

    select

    @SC=Symbol_Code from #FinalROC where

    Transaction_Id=@MinTransId

    --select @SC

    Declare

    @Cnt int,

    @crt int,

    @newcnt int,

    @TCP decimal(18,2),

    @TCP1 decimal(18,2)

    set @Cnt=(select count(*) from EOD_NSE_Stock where Transaction_date

    between dateadd(day, -14, getdate()) and getdate() and Symbol_Code=@SC)

    if (@Cnt<13) begin

    set

    @crt=13-@Cnt set

    @newcnt=@crt+14 IF

    OBJECT_ID(N'tempdb..#ROC_data', N'U') IS NOT NULL drop

    table #ROC_data; /*drop table if necessary*/

    set nocount on

    select row_number() over (order by Transaction_date) n, Transaction_date

    , close_price into

    #ROC_data from

    EOD_NSE_Stock where

    Symbol_Code

    =@SC and

    Transaction_date

    between dateadd

    (day, -@newcnt, getdate()) and getdate() create

    clustered index ix_n on #ROC_data(n)

    --Select * from #ROC_data

    Select

    @TCP=close_price from #ROC_data where

    n=13 Select

    @TCP1= close_price from #ROC_data where

    n=1 if

    (Convert(decimal(18,3),(@TCP-@TCP1)/(@TCP*100))>20) begin

    Update

    #FinalROC set ROC=Convert(decimal(18,3),(@TCP-@TCP1)/(@TCP*100)) where

    Transaction_Id=@MinTransId end

    end

    else

    if (@Cnt=13) begin

    IF

    OBJECT_ID(N'tempdb..#ROC_data1', N'U')

    IS NOT NULL

    drop table #ROC_data1; /*drop table if necessary*/ set

    nocount on

    select

    row_number() over (order by Transaction_date) n,

    Transaction_date,

    close_price into

    #ROC_data1 from

    EOD_NSE_Stock where

    Symbol_Code

    =@SC and

    Transaction_date

    between dateadd

    (day, -14, getdate()) and getdate()

    create

    clustered index ix_n on #ROC_data1(n)

    -- Select * from #ROC_data1

    Select

    @TCP=close_price from #ROC_data1 where

    n=13 Select

    @TCP1= close_price from #ROC_data1 where

    n=1

    Update

    #FinalROC set ROC=Convert(decimal(18,3),(@TCP-@TCP1)/(@TCP*100)) where

    Transaction_Id=@MinTransId

    end

    set @MinTransId=@MinTransId+1

    end

    Select * from #FinalROC

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply