SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 portionate the given amount
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 05/28/2013 :  03:02:00  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/28/2013 :  03:11:04  Show Profile  Reply with Quote
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
Go to Top of Page

arkiboys
Flowing Fount of Yak Knowledge

1418 Posts

Posted - 05/28/2013 :  03:23:38  Show Profile  Reply with Quote
thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/28/2013 :  04:01:05  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000