SQL:在表循环期间金额未增加

huangapple go评论74阅读模式
英文:

SQL: Amount is not incremented during table looping

问题

I understand your request, and I'll provide the translated code portion for you:

declare @registration_nr varchar(20),
   @entity_id varchar(10)
DECLARE @gross_salary float, @net_salary float, @cursid int, @category varchar(50), @value float, @relative numeric(1), @sens numeric(1)

set @registration_nr = '19820506-0-2';
set @entity_id = 'edu7';
SET @gross_salary = (select pay_amount from dbo.EMPLOYEES_PAY where registration_nr = @registration_nr and entity_id = @entity_id and active = 1)

CREATE TABLE #temp
(registration_nr varchar(20),
 category varchar(50),
 valeur float,
 relativite numeric(1),
 sens numeric(1),
 salaire_net float);

DECLARE curs_rowid CURSOR FAST_FORWARD FOR
    SELECT nom_categorie,
           relativite,
           valeur,
           sens
    FROM dbo.SALARY_SETTINGS --This is the table that contains the parameters (insurance,...)
    WHERE code_institution = @entity_id and actif = 1;

OPEN curs_rowid				
    FETCH NEXT FROM curs_rowid INTO @category, @relative, @value, @sens
    WHILE @@fetch_status = 0
        BEGIN
            if @relative = 0
                BEGIN
                    if @sens = 0
                        BEGIN
                            set @net_salary = @gross_salary + (@gross_salary*@value)/100
                            INSERT INTO #temp (category, valeur, relativite, sens, salaire_net)
                            values(@category, @value, @relative, @sens, @net_salary);
                        END;
                    else if @sens = 1
                        BEGIN
                            set @net_salary = @gross_salary - (@gross_salary*@value)/100
                            INSERT INTO #temp (category, valeur, relativite, sens, salaire_net)
                            values(@category, @value, @relative, @sens, @net_salary);
                        END;
                END;
            else if @relative = 1
                BEGIN
                    if @sens = 0
                        BEGIN
                            set @net_salary = @gross_salary + @value
                            INSERT INTO #temp (category, valeur, relativite, sens, salaire_net)
                            values(@category, @value, @relative, @sens, @net_salary);
                        END;
                    else if @sens = 1
                        BEGIN
                            set @net_salary = @gross_salary - @value
                            INSERT INTO #temp (category, valeur, relativite, sens, salaire_net)
                            values(@category, @value, @relative, @sens, @net_salary);
                        END;
                END;
            FETCH NEXT FROM curs_rowid INTO @category, @relative, @value, @sens
        END;					
CLOSE curs_rowid;
DEALLOCATE curs_rowid;

I hope this helps with your project! If you have any more specific questions or need further assistance, please feel free to ask.

英文:

I have to calculate an after tax salary amount based on a gross salary present in one table, and different other parameters present in another table. This is the situation:

I have a salary table that contains the gross salary of employees

SQL:在表循环期间金额未增加

To compute the net amount, I have to either substract or add other parameters (contributions, insurance, ...) based on whether the corresponding value has to be considered as either gross or relative (percentage). Here is the table:

SQL:在表循环期间金额未增加

Logic:

Relativite = 1 means that the value (valeur in the table) is percentage, 0 means it's gross.
Sens = 1 means the value has to be substracted from the salary, 0 means it has to be added.

With this example, what I want to achieve in order to get the net salary is something like this:

1st Line: Net_Salary = (700 - (700*13.4)/100).
2nd Line: Net Salary = value of first Line - 13
3rd LIne: Net Salary = value of 2nd Line - 13000 and so forth...

To achieve this, I have used a cursor that loops through the table and fetches each value to compute the net salary. I end up with something like this:

SQL:在表循环期间金额未增加

The problem with this result is that the amount is not decremented while looping through the table. It always computes based on the original value.

Here is the code I have used:

declare @registration_nr varchar(20),
@entity_id varchar(10)
DECLARE @gross_salary float, @net_salary float, @cursid int, @category varchar(50), @value float, @relative numeric(1), @sens numeric(1)
set @registration_nr = '19820506-0-2';
set @entity_id = 'edu7';
SET @gross_salary = (select pay_amount from dbo.EMPLOYEES_PAY where registration_nr = @registration_nr and entity_id = @entity_id and active = 1)
--set @rowcnt = (select count(1) from dbo.PARAMETRES_SALAIRES where code_institution = @entity_id and actif = 1)
CREATE TABLE #temp
(registration_nr varchar(20),
category varchar(50),
valeur float,
relativite numeric(1),
sens numeric(1),
salaire_net float);
DECLARE curs_rowid CURSOR FAST_FORWARD FOR
SELECT nom_categorie,
relativite,
valeur,
sens
FROM dbo.SALARY_SETTINGS --This is the table that contains the parameters (insurance,...)
WHERE code_institution = @entity_id and actif = 1;
OPEN curs_rowid				
FETCH NEXT FROM curs_rowid INTO @category, @relative, @value, @sens
WHILE @@fetch_status = 0
BEGIN
if @relative = 0
BEGIN
if @sens = 0
BEGIN
set @net_salary = @gross_salary + (@gross_salary*@value)/100
INSERT INTO #temp (category, valeur, relativite, sens, salaire_net)
values(@category, @value, @relative, @sens, @net_salary);
END;
else if @sens = 1
BEGIN
set @net_salary = @gross_salary - (@gross_salary*@value)/100
INSERT INTO #temp (category, valeur, relativite, sens, salaire_net)
values(@category, @value, @relative, @sens, @net_salary);
END;
END;
else if @relative = 1
BEGIN
if @sens = 0
BEGIN
set @net_salary = @gross_salary + @value
INSERT INTO #temp (category, valeur, relativite, sens, salaire_net)
values(@category, @value, @relative, @sens, @net_salary);
END;
else if @sens = 1
BEGIN
set @net_salary = @gross_salary - @value
INSERT INTO #temp (category, valeur, relativite, sens, salaire_net)
values(@category, @value, @relative, @sens, @net_salary);
END;
END;
FETCH NEXT FROM curs_rowid INTO @category, @relative, @value, @sens
END;					
CLOSE curs_rowid;
DEALLOCATE curs_rowid;

Any idea how I can solve this thing and have on the last row the last value that is based on all the previous calculations?

答案1

得分: 2

在以下代码行之后:

SET @gross_salary = (select pay_amount from dbo.EMPLOYEES_PAY where registration_nr = @registration_nr and entity_id = @entity_id and active = 1)

添加:

SET  @net_salary=@gross_salary;

并且在游标部分,将所有的 @gross_salary 替换为 @net_salary

英文:

After the line:

SET @gross_salary = (select pay_amount from dbo.EMPLOYEES_PAY where registration_nr = @registration_nr and entity_id = @entity_id and active = 1)

Add

SET  @net_salary=@gross_salary;

And in the cursor part, replace all @gross_salary with @net_salary

huangapple
  • 本文由 发表于 2020年1月3日 22:18:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/59580124.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定