-
Help in stored procedure with cursor
Please help in creating the stored procedure for
the following code.
Thanks in Advance.
=============================
Create procedure extractdata
@companynumber varchar(3),
@firstdateofmonth int,
@lastdateofmonth int
as
truncate table xt
truncate table xtt
insert into xt(account_code,balance_date,net_change,current_ba lance,oldlob)
select account_code, balance_date,net_change,current_balance,@companynu mber
from ccc@companynumber..glbal where balance_date<=@firstdateofmonth
declare @v1 varchar(32)
declare @v2 int
declare @v3 float
declare c1 cursor for
select account_code, max(balance_date) from xt
where oldlob=@companynumber
group by account_code
open c1
fetch next from c1 into @v1,@v2
WHILE @@FETCH_STATUS = 0
begin
update xt set status='max' where account_code=@v1 and balance_date=@v2
and oldlob=@companynumber
fetch next from c1 into @v1,@v2
end
close c1
deallocate c1
insert into xtt(account_code,balance_date,net_change,current_b alance,oldlob)
select account_code,balance_date,net_change,current_balan ce,oldlob from xt
where status='max' and oldlob=@companynumber
order by account_code
insert into test1(account_code,current_balance,net_change,bala nce_date,oldlob)
select account_code,round(current_balance,2),round(net_ch ange,2),balance_date,@companynumber
from xtt where oldlob=@companynumber
and account_code in (select account_code from xxxmay where oldlob=@companynumber)
update test1 set net_change=0 where balance_date<@lastdateofmonth
and oldlob=@companynumber
select sum(current_balance),sum(net_change) from test1
where oldlob=@companynumber
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|