Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to calculate the Rate Of Change (ROC) Rate Topic Display Mode Topic Options
How to calculate the Rate Of Change (ROC)
alter
 0%
0
proc Roc as
 0%
0
IF
 0%
0
OBJECT_ID(N'tempdb..#FinalROC', N'U') IS NOT NULL drop
 0%
0
table #FinalROC; create
 0%
0
table #FinalROC (
 0%
0
Transaction_Id int, Transaction_Date
 0%
0
datetime, Symbol_Code
 0%
0
varchar(50), Symbol_Name
 0%
0
varchar(50), Close_Price
 0%
0
decimal(18,2), ROC
 0%
0
decimal(18,2), ROC_Flag
 0%
0
varchar(50)) insert
 0%
0
into #FinalROC( Transaction_Id
 0%
0
, Transaction_Date
 0%
0
 Member Votes: 0, Anonymous Votes: 0. You don't have permission to vote within this poll.
Author
 Message
 Posted Monday, September 26, 2011 9:06 AM
 SSC Rookie Group: General Forum Members Last Login: Friday, November 18, 2011 5:04 AM Points: 26, Visits: 39
 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 @SCDeclare @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 endSelect * from #FinalROC
Post #1181064

 Permissions