Adjusted Running Balance - 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

General SQL Server Forums

New to SQL Server Programming

Adjusted Running Balance

Author

Topic

RobinJay
Starting Member

2 Posts

Posted - 2010-07-03 : 18:03:00

Hello -I am trying to write some code for the following problem.

In English....
- Tier 1 gets 2.5% of 100% (which equals 2.5% of 100%), leaving a balance of 97.5%
- Tier 5 gets 10% of 97.5% (which equals 9.75% of 100%), leaving a balance of 87.75%
- Tier 10 gets 1% of 87.75% (which equals .8775% of 100%), leaving a balance of 86.8725%
- Tier 15 gets 2.5% of 86.8725 (which equals 2.1718% of 100%) leaving a balance of 84.700%

I want this as my result:
Tier,Pcnt,ProfitBase,PcntOf100,Balance
1,.025,1,.025,.975
5,.1,.975,.0975,.8775
10,.01,.8775,.008775,.86873
15,0025,.86873,.02171813,084701

Here is the set up:

CREATE TABLE rates
(Tier integer,
Pcnt numeric(9,4))

Insert into rates (Tier,Pcnt) values (1,.025)
Insert into rates (Tier,Pcnt) values (5,.1)
Insert into rates (Tier,Pcnt) values (10,.01)
Insert into rates (Tier,Pcnt) values (15,.025)

Your assistance is appreciated -

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-06 : 07:58:17

Try this:


select
y.tier
,1-y.sum_pcnt as diff_sum_pcnt
from
(
select
r.tier
,r.pcnt
,coalesce(diff,0)+r.pcnt as sum_pcnt
from
rates as r
outer apply
(select sum(pcnt) as diff from rates where tier < r.tier) as r1
)as y

RobinJay
Starting Member

2 Posts

Posted - 2010-07-07 : 21:00:14

This is very handsome, but I cannot integrate it into my real life example. Someone else suggested I use a recursive CTE and I was able to adapt that to get the results I need.

But I do thank you sincerely and will continue to play with your code to better understand it, especially as it is smaller/tighter than the code I currently have -

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources