Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to calculate the Rate Of Change (ROC) Expand / Collapse
How to calculate the Rate Of Change (ROC)
Poll ResultsVotes
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

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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 @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
Post #1181064
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse