This requirement is from a tiered fee structure which based on the total amount and a defined fee structure.

For example:

For under $50, fee is 0.1%

$51 – $100, fee is 0.05%

$101 – $150, fee is 0.02%

$150+, 0.01%

…

Question is:

How much fee is when amount is $120, or $90?

Below is my implementation using recursive query:

–Create Fee structure table

if OBJECT_ID(‘dbo.TieredStructure’) is not null drop table dbo.TieredStructure

create table dbo.TieredStructure

(

layer int,

value decimal(18,2)

)

insert dbo.TieredStructure

values

(50,0.10),

(100,0.05),

(150,0.02),

(99999,0.01) — assumed max money amount

go

–Calculate Fee

declare @total decimal(18,2) = 120.;

with rowTS as

(

select

layer

,value

,ROW_NUMBER() over (order by layer) as row_num

from

dbo.TieredStructure

),

fee as

(

select

row_num

, layer

, value

, layer as mx_range

, @total as remaining_amt

, CAST(case when @total > layer then layer * value else @total * value end as decimal(18,2)) as tiered_fee

from

rowTS

where

row_num = 1

union all

select

r.row_num

, r.layer

, r.value

, r.layer – f.layer as mx_range

, CAST(f.remaining_amt – f.mx_range as decimal(18,2)) as remaining_amt

, CAST(case when f.remaining_amt – f.mx_range > r.layer – f.layer then (r.layer – f.layer) * r.value else (f.remaining_amt – f.mx_range)* r.value end as decimal(18,2)) as tiered_fee

from

rowTS r

inner join fee f on f.row_num = r.row_num -1

where

f.remaining_amt – f.mx_range > 0

)

select SUM(tiered_fee) from fee

Some results:

Fee for $120 is $7.9 = $50 * 0.1 + $50 * 0.05 + $20 * 0.02

Fee for $90 is $7 = $50* 0.1 + $40 * 0.05

Important: This is NOT a ideal design or real life requirement – using recursive query WILL have a significant performance impact. In a realistic design, one way to reduce performance impact is to use persisted computed column to store calculated values.