September 26, 2011 at 9:06 am
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