Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 20th, 2006, 04:36 AM
Registered User
 
Join Date: Jan 2006
Location: , , India.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to improve this C++ code to accept spaces in i code_lover C++ Programming 1 July 23rd, 2008 01:42 AM
Please help me to improve this ADO.NET code. edurazee ADO.NET 6 July 22nd, 2008 09:55 PM
Improve on this code...? niallhannon Pro VB 6 7 February 24th, 2007 01:47 PM
how to improve this code? dhaval229 SQL Language 0 February 20th, 2006 04:38 AM
How to Improve Myself in ASP ranuji BOOK: Access 2003 VBA Programmer's Reference 4 April 22nd, 2005 08:53 AM



All times are GMT -4. The time now is 01:57 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.