有表
salary_detail_present
person_id item_name 金额
p101 所得税 0
p101 应纳税所得额 3000
p102 所得税 0
p102 应纳税所得额 3000
p103 所得税 0
p103 应纳税所得额 3000********************************
下面是我在存储过程中写计算单个person_id(p101)的所得税:
.........................
declare @tax_base numeric,@tax numeric
set @tax_base=((select 金额 from salary_detail_present
where item_name='应纳税所得额' and person_id='p101' )-1600)
if (@tax_base)>5000
set @tax=625+(@tax_base-5000)*0.2
else if @tax_base>2000
set @tax=175+(@tax_base-2000)*0.15
else if @tax_base>500
set @tax=25+(@tax_base-500)*0.1
else if @tax_base>0
set @tax=@tax_base*0.05
else
set @tax=0
update salary_detail_present
set 金额=@tax
where 人员ID='p101'
and item_name='所得税'
end
......................
我想在存储过程中写一段计算所有person_id(p101,p102,.....)的所得税的语句,请问我该如何写?谢谢!
salary_detail_present
person_id item_name 金额
p101 所得税 0
p101 应纳税所得额 3000
p102 所得税 0
p102 应纳税所得额 3000
p103 所得税 0
p103 应纳税所得额 3000********************************
下面是我在存储过程中写计算单个person_id(p101)的所得税:
.........................
declare @tax_base numeric,@tax numeric
set @tax_base=((select 金额 from salary_detail_present
where item_name='应纳税所得额' and person_id='p101' )-1600)
if (@tax_base)>5000
set @tax=625+(@tax_base-5000)*0.2
else if @tax_base>2000
set @tax=175+(@tax_base-2000)*0.15
else if @tax_base>500
set @tax=25+(@tax_base-500)*0.1
else if @tax_base>0
set @tax=@tax_base*0.05
else
set @tax=0
update salary_detail_present
set 金额=@tax
where 人员ID='p101'
and item_name='所得税'
end
......................
我想在存储过程中写一段计算所有person_id(p101,p102,.....)的所得税的语句,请问我该如何写?谢谢!
[所得税]=CASE WHEN [金额]-1600>5000 THEN 625+([金额]-1600-5000)*0.2
WHEN [金额]-1600>2000 THEN 175+([金额]-1600-2000)*0.15
WHEN [金额]-1600>500 THEN 25+([金额]-1600-500)*0.1
ELSE 0 END
FROM [salary_detail_present]
WHERE [item_name]='应纳税所得额'
DECLARE person_idTotal CURSOR FOR
SELECT distinct person_id
FROM salary_detail_present
OPEN person_idTotal
FETCH NEXT FROM person_idTotal INTO @person_id
WHILE @@fetch_status = 0
BEGIN
exec ('person_id '+(@person_id))
end FETCH NEXT FROM person_idTotal INTO @person_id
END
DEALLOCATE table_cur