#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2

    Why does cursor dont update last row


    Hi everyone! i have created a sql query which uses a cursor, and it has its body like this

    CREATE PROCEDURE [dbo].[SP_MutatePhilhealth_for_RankAndFile] AS
    --jpamagsino 12/12/12
    --Note: before running this procedure, make sure that all SALARY computations has the same date_from.
    /*
    select ntadjremarks4,adjremarks4,nto_remarksdduc4,o_dduc4,* from payregister where
    ntadjremarks4 is not null and ntadjremarks4 <> ''
    or
    adjremarks4 is not null and adjremarks4 <> ''
    or
    nto_remarksdduc4 is not null and nto_remarksdduc4 <> ''
    or
    o_dduc4 is not null and o_dduc4 <> ''


    these fields would be updated when adjustments would be made, so if a flag is to be created, it requires a trigger to manipulate the data, use the trigger After update because the trigger should be
    "triggered" by an update on one of the 4 columns


    */
    declare @payperiod as smalldatetime
    declare @emp_cde integer
    declare @basicpay float
    declare @Philhealth float
    declare @netpay decimal(20,2)
    declare @BEnetpay decimal(20,2)
    declare @philhealth2pass float
    declare @table_name varchar(30)



    set @payperiod = (select max(date_from) from payregister where setup = 'S' and rank = 'R')
    set @table_name=rtrim('payregister_bak'+substring(convert(char(10),getdate(),120),1,4)+substring(convert (char(10),getdate(),120),6,2)+substring(convert(char(10),getdate(),120),9,2) )


    BEGIN



    /*
    select * into payregister_bak from payregister where date_from = @payperiod
    exec sp_rename 'payregister_bak', @table_name
    */

    /*
    select *
    into @tablename
    --print @table_name
    from payregister
    */








    DECLARE cursorName CURSOR
    LOCAL SCROLL STATIC

    FOR

    Select a.emp_cde FROM payregister as a inner join employee as b on a.emp_cde = b.emp_cde where b.emp_stat <> 'RS' and a.date_from = @payperiod and b.rank = 'R&F' --order by a.emp_cde asc

    OPEN cursorName

    FETCH NEXT FROM cursorName

    INTO @emp_cde






    WHILE @@FETCH_STATUS = 0



    BEGIN

    FETCH NEXT FROM cursorName

    INTO @emp_cde

    set @basicpay = round((select basicrate from payregister where emp_cde = @emp_cde and date_from = @payperiod),2)
    --jpamagsino 20121214 commented below code for testing purposes.
    --set @philhealth = round((select dbo.Contribution(@emp_cde,0,'PHICempy',@payperiod,@basicpay,@basicpay)),2)


    set @BEnetpay = round((select net_pay from payregister where emp_cde = @emp_cde and date_from = @payperiod),2)

    set @Philhealth = round((select dbo.NewContribution(@emp_cde,0,'PHICempy',@payperiod,@BEnetpay,@basicpay)),2)


    set @netpay =
    (select round(isnull(gross_pay,0),2)
    + round(isnull(adjstment,0),2)
    - (round(isnull(withholding_tax,0),2)
    + round(isnull(sss_ee_contri ,0),2)
    + round(isnull(@Philhealth ,0),2)
    + round(isnull(hdmf_ee_contri ,0),2)
    + round(isnull(due_amt ,0),2)
    + round(isnull(hdmf_loan_dduc ,0),2)
    + round(isnull(co_loan_dduc ,0),2)
    + round(isnull(sss_loan_dduc ,0),2)
    + round(isnull(othr_advnces ,0),2)
    + round(isnull(taxablededuc ,0),2)
    + round(isnull(othr_dduc1 ,0),2)
    + round(isnull(othr_dduc2 ,0),2)
    + round(isnull(othr_dduc3,0),2)
    + round(isnull(othr_dduc4,0),2)) from payregister as b
    inner join employee as a on a.emp_cde = b.emp_cde
    where a.emp_cde = @emp_cde and date_from = @payperiod)


    --set @Philhealth = round((select dbo.NewContribution(@emp_cde,0,'PHICempy',@payperiod,@netpay,@basicpay)),2)

    /*
    uncomment this part then comment the update part for checking

    select b.emp_cde,b.med_er_contri, @basicpay, @philhealth
    from payregister as b
    inner join employee as a on a.emp_cde = b.emp_cde
    where b.emp_cde = @emp_cde and date_from = @payperiod
    */
    /*
    select
    b.emp_cde,b.med_er_contri,@basicpay,@philhealth,@payperiod,
    round(round(isnull(gross_pay,0),2)+ round(isnull(adjstment,0),2)
    - (round(isnull(withholding_tax,0),2)
    + round(isnull(sss_ee_contri ,0),2)
    + round(isnull(@philhealth ,0),2)
    + round(isnull(hdmf_ee_contri ,0),2)
    + round(isnull(due_amt ,0),2)


    + round(isnull(hdmf_loan_dduc ,0),2)
    + round(isnull(co_loan_dduc ,0),2)
    + round(isnull(sss_loan_dduc ,0),2)
    + round(isnull(othr_advnces ,0),2)
    + round(isnull(taxablededuc ,0),2)
    + round(isnull(othr_dduc1 ,0),2)
    + round(isnull(othr_dduc2 ,0),2)
    + round(isnull(othr_dduc3,0),2)
    + round(isnull(othr_dduc4,0),2)),2),net_pay
    from payregister as b
    inner join employee as a on a.emp_cde = b.emp_cde
    where a.emp_cde = @emp_cde and date_from = @payperiod
    */
    --select @emp_cde


    update b set b.med_er_contri = @Philhealth, b.med_ee_contri = @Philhealth, net_pay = @netpay, adjusted = '2'
    from payregister as b
    inner join employee as a on a.emp_cde = b.emp_cde
    where
    b.emp_cde = @emp_cde and
    date_from = @payperiod
    and adjusted is null
    or
    b.emp_cde = @emp_cde and
    date_from = @payperiod
    and adjusted = 0
    or
    b.emp_cde = @emp_cde and
    date_from = @payperiod
    and adjusted = 1





    END


    END



    CLOSE cursorName

    DEALLOCATE cursorName
    GO

    why cant it update the last record?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    101
    Rep Power
    9
    try that
    you
    used
    Code:
    FETCH NEXT FROM cursorName
    	INTO @emp_cde
    twice just after when open cursor
    it should be ones when you open cursor and second time after your update statement

    (I removed you commented code to make easy to read

    Code:
    CREATE PROCEDURE [dbo].[SP_MutatePhilhealth_for_RankAndFile] AS
    
    declare @payperiod as smalldatetime
    declare @emp_cde integer
    declare @basicpay float
    declare @Philhealth float
    declare @netpay decimal(20,2)
    declare @BEnetpay decimal(20,2)
    declare @philhealth2pass float
    declare @table_name varchar(30)
    
    
    
    set @payperiod = (select max(date_from) from payregister where setup = 'S' and rank = 'R')
    set @table_name=rtrim('payregister_bak'+substring(convert(char(10),getdate(),120),1,4)+substring(convert (char(10),getdate(),120),6,2)+substring(convert(char(10),getdate(),120),9,2) )
    
    
    BEGIN
    
    DECLARE cursorName CURSOR
    	FOR Select a.emp_cde 
    			FROM payregister as a 
    			inner join employee as b 
    				on a.emp_cde = b.emp_cde 
    			where b.emp_stat <> 'RS' 
    				and a.date_from = @payperiod 
    				and b.rank = 'R&F' --order by a.emp_cde asc
    
    OPEN cursorName
    	FETCH NEXT FROM cursorName
    	INTO @emp_cde
    	WHILE @@FETCH_STATUS = 0
    	BEGIN
    	set @basicpay = round((select basicrate from payregister where emp_cde = @emp_cde and date_from = @payperiod),2)
    	set @BEnetpay = round((select net_pay from payregister where emp_cde = @emp_cde and date_from = @payperiod),2)
    	set @Philhealth = round((select dbo.NewContribution(@emp_cde,0,'PHICempy',@payperiod,@BEnetpay,@basicpay)),2)
    	set @netpay =
    			(select round(isnull(gross_pay,0),2)
    			+ round(isnull(adjstment,0),2)
    			- (round(isnull(withholding_tax,0),2)
    			+ round(isnull(sss_ee_contri ,0),2)
    			+ round(isnull(@Philhealth ,0),2)
    			+ round(isnull(hdmf_ee_contri ,0),2)
    			+ round(isnull(due_amt ,0),2)
    			+ round(isnull(hdmf_loan_dduc ,0),2)
    			+ round(isnull(co_loan_dduc ,0),2)
    			+ round(isnull(sss_loan_dduc ,0),2)
    			+ round(isnull(othr_advnces ,0),2)
    			+ round(isnull(taxablededuc ,0),2)
    			+ round(isnull(othr_dduc1 ,0),2)
    			+ round(isnull(othr_dduc2 ,0),2)
    			+ round(isnull(othr_dduc3,0),2)
    			+ round(isnull(othr_dduc4,0),2)) from payregister as b
    			inner join employee as a on a.emp_cde = b.emp_cde
    			where a.emp_cde = @emp_cde and date_from = @payperiod)
    
    			update b set b.med_er_contri = @Philhealth, b.med_ee_contri = @Philhealth, net_pay = @netpay, adjusted = '2'
    				from payregister as b
    					inner join employee as a on a.emp_cde = b.emp_cde
    				where
    					b.emp_cde = @emp_cde and
    					date_from = @payperiod
    					and adjusted is null
    					or
    					b.emp_cde = @emp_cde and
    					date_from = @payperiod
    					and adjusted = 0
    					or
    					b.emp_cde = @emp_cde and
    					date_from = @payperiod
    					and adjusted = 1
    			FETCH NEXT FROM cursorName
    			INTO @emp_cde
    		END
    	END
    CLOSE cursorName
    DEALLOCATE cursorName
    GO
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2
    Originally Posted by gk53
    try that
    you
    used
    Code:
    FETCH NEXT FROM cursorName
    	INTO @emp_cde
    twice just after when open cursor
    it should be ones when you open cursor and second time after your update statement

    (I removed you commented code to make easy to read

    Code:
    CREATE PROCEDURE [dbo].[SP_MutatePhilhealth_for_RankAndFile] AS
    
    declare @payperiod as smalldatetime
    declare @emp_cde integer
    declare @basicpay float
    declare @Philhealth float
    declare @netpay decimal(20,2)
    declare @BEnetpay decimal(20,2)
    declare @philhealth2pass float
    declare @table_name varchar(30)
    
    
    
    set @payperiod = (select max(date_from) from payregister where setup = 'S' and rank = 'R')
    set @table_name=rtrim('payregister_bak'+substring(convert(char(10),getdate(),120),1,4)+substring(convert (char(10),getdate(),120),6,2)+substring(convert(char(10),getdate(),120),9,2) )
    
    
    BEGIN
    
    DECLARE cursorName CURSOR
    	FOR Select a.emp_cde 
    			FROM payregister as a 
    			inner join employee as b 
    				on a.emp_cde = b.emp_cde 
    			where b.emp_stat <> 'RS' 
    				and a.date_from = @payperiod 
    				and b.rank = 'R&F' --order by a.emp_cde asc
    
    OPEN cursorName
    	FETCH NEXT FROM cursorName
    	INTO @emp_cde
    	WHILE @@FETCH_STATUS = 0
    	BEGIN
    	set @basicpay = round((select basicrate from payregister where emp_cde = @emp_cde and date_from = @payperiod),2)
    	set @BEnetpay = round((select net_pay from payregister where emp_cde = @emp_cde and date_from = @payperiod),2)
    	set @Philhealth = round((select dbo.NewContribution(@emp_cde,0,'PHICempy',@payperiod,@BEnetpay,@basicpay)),2)
    	set @netpay =
    			(select round(isnull(gross_pay,0),2)
    			+ round(isnull(adjstment,0),2)
    			- (round(isnull(withholding_tax,0),2)
    			+ round(isnull(sss_ee_contri ,0),2)
    			+ round(isnull(@Philhealth ,0),2)
    			+ round(isnull(hdmf_ee_contri ,0),2)
    			+ round(isnull(due_amt ,0),2)
    			+ round(isnull(hdmf_loan_dduc ,0),2)
    			+ round(isnull(co_loan_dduc ,0),2)
    			+ round(isnull(sss_loan_dduc ,0),2)
    			+ round(isnull(othr_advnces ,0),2)
    			+ round(isnull(taxablededuc ,0),2)
    			+ round(isnull(othr_dduc1 ,0),2)
    			+ round(isnull(othr_dduc2 ,0),2)
    			+ round(isnull(othr_dduc3,0),2)
    			+ round(isnull(othr_dduc4,0),2)) from payregister as b
    			inner join employee as a on a.emp_cde = b.emp_cde
    			where a.emp_cde = @emp_cde and date_from = @payperiod)
    
    			update b set b.med_er_contri = @Philhealth, b.med_ee_contri = @Philhealth, net_pay = @netpay, adjusted = '2'
    				from payregister as b
    					inner join employee as a on a.emp_cde = b.emp_cde
    				where
    					b.emp_cde = @emp_cde and
    					date_from = @payperiod
    					and adjusted is null
    					or
    					b.emp_cde = @emp_cde and
    					date_from = @payperiod
    					and adjusted = 0
    					or
    					b.emp_cde = @emp_cde and
    					date_from = @payperiod
    					and adjusted = 1
    			FETCH NEXT FROM cursorName
    			INTO @emp_cde
    		END
    	END
    CLOSE cursorName
    DEALLOCATE cursorName
    GO
    THANKS! really stupid of me to now know that... hahahaha thanks alot!


IMN logo majestic logo threadwatch logo seochat tools logo