portionate the given amount - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

portionate the given amount

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-05-28 : 03:02:00

Hello,
Given an amount, I would like to apply different rates to different portions of that amount as follows:

declare @Total_Value float = 180000000

declare @Total_Value_Portion1 float = 10000000
declare @Total_Value_Portion2 float = 25000000
declare @Total_Value_Portion3 float = 50000000
declare @Total_Value_Portion4 float = 150000000
declare @Total_Value_Portion5 float -- represents value greater than @Total_Value_Portion4

declare @FeeRate_Portion1 decimal(15, 2) = 0.6
declare @FeeRate_Portion2 decimal(15, 2) = 0.6
declare @FeeRate_Portion3 decimal(15, 2) = 0.5
declare @FeeRate_Portion4 decimal(15, 2) = 0.35
declare @FeeRate_Portion5 decimal(15, 2) = 0.3

declare @CRate_Portion1 decimal(5, 3)
declare @CRate_Portion2 decimal(5, 3)
declare @CRate_Portion3 decimal(5, 3)
declare @CRate_Portion4 decimal(5, 3)
declare @CRate_Portion5 decimal(5, 3)

Looking at the above values, I would like to apply each @FeeRate_Portion accordingly to the @Total_Value
For example,
1- taking the above given @Total_Value as 180 million.

2- the first 10 million of @Total_Value is calculated as:
@CRate_Portion1 = @Total_Value_Portion1 * @FeeRate_Portion1

3- the next 25 million of @Total_Value is calculated as:
@CRate_Portion2 = (@Total_Value_Portion2 - @Total_Value_Portion1) * @FeeRate_Portion2

4- the next 50 million of @Total_Value is calculated as:
@CRate_Portion3 = (@Total_Value_Portion3 - @Total_Value_Portion2) * @FeeRate_Portion3

5- the next 150000000 million of @Total_Value is calculated as:
@CRate_Portion4 = (@Total_Value_Portion4 - @Total_Value_Portion3) * @FeeRate_Portion4

6- any figure after 150000000 million of @Total_Value is calculated as:
@CRate_Portion5 = (@Total_Value_Portion5 - @Total_Value) * @FeeRate_Portion5

How is this done please?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 03:11:04

you can populate a table with Portions as well as FeeRatePortion values and then do lke below


declare @portion table
(
PortionID int,
TotalValuePortion float,
FeeRatePortion decimal(5,3)
)

declare @Total_Value float = 180000000

insert @portion
values (1,10000000,0.6),
(2,25000000,0.6),
(3,50000000,0.5),
(4,150000000,0.35),
(5,150000001,0.3)

select ( TotalValuePortion - case when id= max(id) over () then @Total_Value  else coalesce(prevtotal,0) end ) * FeeRatePortion
from @portion p
outer apply (select max(TotalValuePortion ) as prevtotal
             from @portion
             where id < p.id
            )p1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2013-05-28 : 03:23:38

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-28 : 04:01:05

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources