August 4, 2009 at 3:51 am
i create a store procedure in which i use multiple cursor.
Problem when @@fetch_status =0 and for which cursor.
@@fetch_status is a static variable then how can i identify that @@fetch_status for which cursor.
navtotal
alter proc NavTotal
as
begin
declare @category int
declare @Scheme_Name varchar(200)
declare @count int
declare @val1 float --new nav value
declare @val2 float --old nav value of 1 week
declare @val3 float --store variable for manupulation
declare @val4 float --nav value before 1 day
declare @row_num int
declare @new_nav_date datetime
declare @old_nav_date datetime
declare @nav_date datetime
declare @backDate datetime
declare @table table
(
scheme_name varchar(200),
new_nav float,
old_nav float,
diff float,
new_nav_date datetime,
old_nav_date datetime,
inc1w float,
inc1day float,
backdate datetime,
category int
)
declare @tableTotal table
(
scheme_name varchar(200),
new_nav float,
old_nav float,
diff float,
new_nav_date datetime,
old_nav_date datetime,
inc1w float,
inc1day float,
backdate datetime,
category int
)
declare curCategory cursor
for select distinct category from nav_detail order by category asc
open curCategory
fetch next from curCategory into @category
while @@fetch_status=0
begin
DECLARE
cur CURSOR
FOR select distinct scheme_name from nav_detail where category=@category
open cur
fetch
NEXT from cur into @Scheme_Name
while @@fetch_status=0
begin
declare cur1 cursor for select top 5 net_asset_value,ROW_NUMBER() OVER (ORDER BY date desc) AS ROW,date from nav_detail where scheme_name=@scheme_name order by date desc
open cur1
set @count=1;
fetch next from cur1 into @val3,@row_num,@nav_date
while @@fetch_status=0
begin
if(@count=1)
begin
set @val1=@val3
set @new_nav_date=@nav_date
end
else if(@count=5)
begin
set @val2=@val3
set @old_nav_date=@nav_date
end
else if(@count=2)
begin
set @val4=@val3
set @backDate=@nav_date
end
set @count=@count+1
fetch next from cur1 into @val3,@row_num,@nav_date
end
if(@val2 <>0 and @val4<>0)
begin
set @val3=cast(((@val1-@val2)*100)/@val2 as numeric(18,2))
insert into @table select @scheme_name,cast(@val1 as numeric(18,2)),cast(@val2 as numeric(18,2)),cast(@val1-@val2 as numeric(18,2)),@new_nav_date,@old_nav_date,@val3 ,cast(((@val1-@val4)*100)/@val4 as numeric(18,2)),@backDate,@category
end
else
begin
insert into @table(scheme_name,new_nav,old_nav) select @scheme_name,@val1,@val2
end
close cur1
DEALLOCATE cur1
fetch
NEXT from cur into @Scheme_Name
end
close cur
DEALLOCATE cur
insert into @tableTotal select distinct top 5 * from @table order by inc1w desc
fetch next from curCategory into @category
end
close curCategory
deallocate curCategory
select * from @tableTotal order by category
end
August 4, 2009 at 4:45 am
my main problem is that
I have multiple category
In each category->Multiple Scheme Name
in each shceme name have multiple price based on day wise.
problem is that i want a result on category wise that
in each category->for each scheme->variation of price from today to back day.
August 13, 2009 at 11:53 pm
First, you may nneed a cursor. To really help you, we need your help. Please provide the DDL for the tables, sample data for the tables, expected results based on the sample data.
Please read the first article referenced in my signature block regarding asking for assistance. Follow the instruction in that article regatding creating and posting the information requested. You will find that given this additional info, several people will probably jump in to help and you will get tested code in return.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply