[Leaf function sharing] Calculate personal income tax function

-- ===================================================== ======

-- 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

*/