Tiered Fee Structure using recursive query

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s