how to improve this code?
hello friends
how to improve this code given below:
ALTER procedure Sp_NSE_DataPrepForRemissorCalc
@client_code varchar(10)
as
DECLARE
@remissor_code varchar(10),
@party_Code varchar(10), --displays code of party
@trade_no varchar(17),
@scrip_cd varchar(10),--displays scrip
@tradeqty int,-- displays quantity of trade
@series varchar(3),
@marketrate money,
@sauda_date datetime,--displays date and time of transaction
@trade_type char(1),
@leg_type CHAR(1),
@sell_buy INT,--says whether it is buy or sell
@sett_sales money,
@normal money,
@brk_100rs money,
@nbrokapp MONEY,-- displays brokerage applied
@min_shr money,
@sett_no VARCHAR(7),
@nsertax money,
@ins_chrg money,
@broker_chrg money,
@turn_tax money,
@billflag int,
@sub_broker varchar(10),
@table_no varchar(5),
@scheme_type varchar(5),
@to_date datetime,
@val_perc char(1),
@testbs INT,
@testqty int
begin
--creation of table
drop table trd_table
create table trd_table (remissor_code varchar(10) NOT NULL,party_code VARCHAR(10) NOT NULL, sauda_date datetime NOT NULL, sett_no VARCHAR(7) NOT NULL, trade_no varchar(17) NOT NULL, scrip_cd varchar(10) NOT NULL, series CHAR(3) NOT NULL,
tradeqty int NOT NULL, trade_type char(1) NOT NULL, leg_type char(1) NOT NULL, sell_buy INT NOT NULL, marketrate money NOT NULL, nsertax money NOT NULL, ins_chrg money NOT NULL, broker_chrg money NOT NULL, turn_tax money NOT NULL,
brk_100rs money NOT NULL, brk_shr MONEY NOT NULL, min_shr money NOT NULL)
---- declaring 1st cursor
DECLARE distinct_partycode CURSOR FOR
select distinct client.sub_broker,trade.party_Code
from nseclient1 client,nsetrades trade
where trade.contractno <> 0 and trade.sauda_date between 'Dec 1 2005' and 'Dec 31 2005'
and trade.party_code=client.cl_code and left(ltrim(client.sub_broker),1) in (7,8)
and len(ltrim(rtrim(sub_broker))) = 7
--- opening 1st cursor
OPEN distinct_partycode
--- fetching values from 1st cursor for 1st time
FETCH NEXT FROM distinct_partycode
INTO @sub_broker,@party_code
-- looping thru first cursor
WHILE @@FETCH_STATUS = 0
BEGIN
------- declaring 2nd cursor
declare distinct_brokerage cursor for
select distinct trade.party_code, trade.sett_sales,scheme.table_no,scheme.to_date,br oktable.[normal]
from nsetrades trade,nseclientbrok_scheme scheme,nsebroktable broktable
where scheme.table_no = broktable.table_no and scheme.party_code = trade.party_code and scheme.trade_type = 'NRM'
and scheme.to_date like 'dec 31 2049%' and broktable.val_perc = 'P' and scheme_type = 'DEL'
------- opening 2nd cursor
open distinct_brokerage
------- fetching value form 2nd cursor for 1st time
fetch next from distinct_brokerage
into @party_code,@sett_sales,@table_no,@to_date,@normal
------- looping through second cursor
while @@fetch_status = 0
begin
------- declaring 3rd cursor
declare distinct_trades cursor for
select trade_no,scrip_cd,marketrate,sell_buy,Tradeqty,nbr okapp,sauda_date,sett_no,nsertax,ins_chrg,broker_c hrg,turn_tax,billflag
from nsetrades
where party_code = @client_code and scrip_cd = @scrip_cd and series = @series and sett_no = @sett_no
order by sauda_date --convert(varchar,sauda_date,109)
--- opening of 3rd cursor
open distinct_trades
--- fetching values from 3rd cursor for the 1st time
fetch next from distinct_Trades
into @trade_no,@scrip_cd,@marketrate,@sell_buy,@Tradeqt y,@nbrokapp,@sauda_date,@sett_no,@nsertax,@ins_chr g,@broker_chrg,@turn_tax,@billflag
set @testbs = @sell_buy
set @testqty = 0
--set @trade_type = 'TRD'
/* If @billflag in (2,3)
begin
set @trade_type = 'TRD'
end
If @billflag in (4,5)
begin
-- set @leg_type = ' '
set @trade_type = 'DEL'
-- set @leg_type = ' '
end
*/
-- set @brk_100rs = @sett_sales
set @min_shr = 0.0000
---looping thru 2rd cursor
while @@fetch_status = 0
begin
If @billflag in (2,3)
begin
set @trade_type = 'T'
set @brk_100rs = @sett_sales
if @sell_buy <> @testbs
begin
if @testqty >= @tradeqty
begin
set @testqty = @testqty - @tradeqty
insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
brk_100rs, brk_shr, min_shr)
values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@tradeqty,@trade_typ e,'S',@sell_buy,@marketrate,@nsertax,@ins_chrg,@br oker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@min_shr)
end
else
begin
insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
brk_100rs, brk_shr, min_shr)
values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@testqty,@trade_type ,'S',@sell_buy,@marketrate,@nsertax,@ins_chrg,@bro ker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@min_shr)
set @testqty = @tradeqty - @testqty
set @testbs = @sell_buy
insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
brk_100rs, brk_shr, min_shr)
values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@testqty,@trade_type ,'F',@sell_buy,@marketrate,@nsertax,@ins_chrg,@bro ker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@min_shr)
end
end
else
begin
set @testqty = @testqty + @tradeqty
insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
brk_100rs, brk_shr, min_shr)
values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@tradeqty,@trade_typ e,'F',@sell_buy,@marketrate,@nsertax,@ins_chrg,@br oker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@min_shr)
end
end
If @billflag in (4,5)
begin
set @leg_type = 'D'
set @trade_type = 'D'
set @brk_100rs = @normal
-- set @leg_type = ' '
if @sell_buy <> @testbs
begin
if @testqty >= @tradeqty
begin
set @testqty = @testqty - @tradeqty
insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
brk_100rs, brk_shr, min_shr)
values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@tradeqty,@trade_typ e,@leg_type,@sell_buy,@marketrate,@nsertax,@ins_ch rg,@broker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@mi n_shr)
end
else
begin
insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
brk_100rs, brk_shr, min_shr)
values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@testqty,@trade_type ,@leg_type,@sell_buy,@marketrate,@nsertax,@ins_chr g,@broker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@min _shr)
set @testqty = @tradeqty - @testqty
set @testbs = @sell_buy
insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
brk_100rs, brk_shr, min_shr)
values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@testqty,@trade_type ,@leg_type,@sell_buy,@marketrate,@nsertax,@ins_chr g,@broker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@min _shr)
end
end
else
begin
set @testqty = @testqty + @tradeqty
insert into trd_table (remissor_code,party_code, sauda_date, sett_no, trade_no, scrip_cd, series, tradeqty, trade_type, leg_type, sell_buy, marketrate, nsertax, ins_chrg, broker_chrg, turn_tax,
brk_100rs, brk_shr, min_shr)
values(@sub_broker,@party_code,@sauda_date,@sett_n o,@trade_no,@scrip_cd,@series,@tradeqty,@trade_typ e,@leg_type,@sell_buy,@marketrate,@nsertax,@ins_ch rg,@broker_chrg,@turn_tax,@brk_100rs,@nbrokapp,@mi n_shr)
end
end
---fetching from 3rd cursor
fetch next from distinct_Trades
into @trade_no,@scrip_cd,@marketrate,@sell_buy,@Tradeqt y,@nbrokapp,@sauda_date,@sett_no,@nsertax,@ins_chr g,@broker_chrg,@turn_tax,@billflag
end
CLOSE distinct_trades
DEALLOCATE distinct_trades
---fetching from 2nd cursor
fetch next from distinct_brokerage
into @party_code,@sett_sales,@table_no,@to_date,@normal
end
CLOSE distinct_brokerage
DEALLOCATE distinct_brokerage
--fetching from 1st cursor
FETCH NEXT FROM distinct_partycode
INTO @sub_broker,@party_code
END
select * from trd_table
CLOSE distinct_partycode
DEALLOCATE distinct_partycode
end
dhaval joshi
|
