-- ===================================================== ======
-- Author: maco_wang
-- Create Date: 2011-03-<create date,>
-- Description: refer to the htl258 (Tony) idea to rewrite the function for calculating the tax.
-- ===================================================== ======
Create Function taxrateofpersonal
(
@ Fvalue numeric (18, 4)
)
Returns numeric (18, 4)
As
Begin
Declare @ I numeric (18, 4)
Declare @ basetable table (ID int,
Basemoney numeric (18, 4), minvalue numeric (18, 4 ),
Maxvalue numeric (18,4), taxs numeric (18,4 ))
Insert into @ basetable
Select 0.05, Union all
Select 0.1, Union all
Select 0.15, Union all
Select 0.2, Union all
Select 0.25
Select @ I = sum (case when @ fvalue> basemoney + maxvalue
Then maxvalue-minvalue else @ fvalue-basemoney-minvalue end * taxs)
From @ basetable where basemoney + minvalue <= @ fvalue
Return @ I
End
-- Test example
Select DBO. taxrateofpersonal (2500)
Select DBO. taxrateofpersonal (3500)
Select DBO. taxrateofpersonal (5000)
Select DBO. taxrateofpersonal (9500)
-- Running result
/*
25.0000
100.0000
250.0000
1000.0000
*/