英文:
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
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:
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:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论