We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

# SQL 2005 Running total with percent

on
Medium Priority
323 Views
I need to create a query whereby I start with a base value, then keep a running total value by a percentage. The starting period will always be 0% and \$1.00. From there, I need to build out the rest of the table based on the given percents for each period.  I can do this with a loop; however, am trying to come up with a way without using a loop structure in SQL.

For example:
Period    Percent Change    Total
200810        .00                 \$1.00
200811        .02                 NULL
200812        .05                 NULL

I need to fill in the NULLs like this:
Period    Percent Change    Total
200810        .00                 \$1.00
200811        .02                 \$1.02
200812        .05                 \$1.07

Thanks..

Comment
Watch Question

## View Solutions Only

Senior .Net Developer
CERTIFIED EXPERT

Commented:

Commented:
Will the PERIOD always be sequential or just increasing?

Try this:

``````;with Periods as
(select period,PercentChange,Total,row_number() over(order by period) rn
from YourTable)
select p1.Period, p1.PercentChange, Total=coalesce(p1.Total,(p2.total+(p2.total*p1.percentChange))
from Periods p1
left join Periods p2
on p1.rn=p2.rn+1
``````
Senior .Net Developer
CERTIFIED EXPERT

Commented:

Commented:
Here you go -

``````SELECT t1.Period,
t1.PercentChange,
total = isnull((select min(t2.total) + sum(t2.percentchange)from <table> t2 where t2.period <= t1.period), 0)
from <table> t1
order by period
``````

Commented:
BrandonGalderisi, the PERIOD will always be sequential and increasing. Thx.

Commented:
Ok.  Mine should handle it even if it isn't though since I added in the row number.
Data Engineer
CERTIFIED EXPERT

Commented:
check this
``````select t1.Period, t1.Percent_Change,
1 + (select sum(Percent_Change) from YourTable t2 where t2.period <= t1.period) as Total
from YourTable t1
``````

Commented:
BrandonGalderisi, I have taken your suggestion. Though, is headed in correct direction; it only calculates row 2. Row 3 and 4 are still NULL:

;with Periods as
(select period, BasePerformance, BasePerfAmt, row_number() over(order by period) rn
from #tblItems)
select
p1.Period,
p1.BasePerformance,
Total=coalesce(p1.BasePerfAmt,(p2.BasePerfAmt+(p2.BasePerfAmt*p1.BasePerformance)))
from Periods p1
left join Periods p2
on p1.rn=p2.rn+1

Yields the following:
2008-09-30       0.000000000000000000      1.000000
2008-10-01       0.025980000000000000      1.025980
2008-11-01       0.029996000000000000      NULL
2008-12-01       0.040235000000000000      NULL

Row 3 should be, in the above example, 1.05675529608
Row4 should be 1.09927384541777788

Row 3 is derived from 1.025980 * (1 + .0299996)
and so forth...

Thanks for looking at this.

Commented:
Ok... Let me fix that :(

Commented:
Is period datetime?

Commented:
Try this:

``````;with Periods as (
select period, BasePerformance, BasePerfAmt
from #tblItems
where BasePerfAmount is not null
union all
select a.period, a.BasePerformance, b.BasePerfAmt + (b.BasePerfAmt * a.BasePerformance)
from #tblItems a
join Periods b
on a.Period = b.period+1
)
select * from periods
``````

Commented:
reb73, thanks for your reply. However, since I am dealing with %, I can't sum the percentages as in your example. Here's the math:

1 * (1 + .02) = 1.02
Next,
1.02 * (1 + .05) = 1.071

In your example, the math on the second row would be:
1.00 * (1 + .07) = 1.07

In this case, 1.07 is very close to 1.071. However, over a long track record and with a decimal precision of (38, 18); this sets the results very askew over a long time period. For what I am working on, my precision must be much higher.

The concept, however, that you present is exactly what I am trying to accomplish. Thanks.

Commented:
BrandonGalderisi, yes. In my real case, period is defined as datetime. For what I am doing, I can redefine as a simple integer representation of the period, also.

Commented:
If "PERIOD" is a datetime stored as date only ('2009-02-13 00:00:00.000') then what I have in http:#a23637204 should work.

If you have times, then replace

a.period and period

with

and

respectively

Commented:
Yes, I made the change like so:

;with Periods as (
select period, BasePerformance, BasePerfAmt
from #tblItems
where BasePerfAmt is not null
union all
select a.period, a.BasePerformance, b.BasePerfAmt + (b.BasePerfAmt * a.BasePerformance)
from #tblItems a
join Periods b
on a.Period = dateadd(month, 1, b.period)
)
select * from periods

However, am getting error:
Msg 240, Level 16, State 1, Line 3
Types don't match between the anchor and the recursive part in column "BasePerfAmt" of recursive query "Periods".

am still looking at it...

Thx.
Commented:
What datatype is baseperformance?

replace it where "PUT THERE DATATYPE OF BasePerformance here" is below.
``````;with Periods as (
select period, BasePerformance, cast(BasePerfAmt as "PUT THERE DATATYPE OF BasePerformance here")
from #tblItems
where BasePerfAmt is not null
union all
select a.period, a.BasePerformance, b.BasePerfAmt + (b.BasePerfAmt * a.BasePerformance)
from #tblItems a
join Periods b
on a.Period = dateadd(month, 1, b.period)
)
select * from periods
``````

Not the solution you were looking for? Getting a personalized solution is easy.

Commented:
That handled the error... but, am only getting the one row:
;with Periods as (
select period, BasePerformance, cast(BasePerfAmt as decimal(38,18)) as BasePerfAmt
from #tblItems
where BasePerfAmt is not null
union all
select a.period, a.BasePerformance,
cast(b.BasePerfAmt + (b.BasePerfAmt * a.BasePerformance) as decimal(38,18))
from #tblItems a
join Periods b
on a.Period = dateadd(month, 1, b.period)
)
select * from periods

Results in:
2008-09-30 00:00:00.000      0.000000000000000000      1.000000000000000000

Due to the "where BasePerfAmt is not null" it only returns the first row that has the initial value.

Thanks.

Commented:
2008-09-30       0.000000000000000000      1.000000
2008-10-01       0.025980000000000000      1.025980

Ok.  I saw these two lines with back to back dates.  From there they continue monthly.

BrandonGalderisi, the PERIOD will always be sequential and increasing.

They aren't sequential.  Let me throw row_number back in.
Commented:
The way to fix this would be to add an identity value into #tblItems and insert with an order by statement on period.
Commented:
If I add in row_number(), that is just doing after the fact what should be done when the table is created.  The row_number() method would be used if you are running against a physical table where you can't apply a sequential ID.

If you add an identity INT field called ident to #tblItems, then change this line:

on a.Period = dateadd(month, 1, b.period)

To:

on a.ident = b.ident+1

Commented:
For those who suggested the running totals examples; I had initially thought of that when I embarqed on this process. However, that does not quite cut it. The issue that is eluding me, is how to track the derived value. The formula is amount = TheCalcedValueFromPriorRow * (1 + RateFromCurrentRow)

When doing a running total, you already have the totals which you are summing stored in the table. In my case, I have to calculate the value that I will be using for the next row, and the next, and so forth. Each row builds upon the previous derived value.

Thus far, I have not been able to come up with a way to do this without a loop.

Thanks all.

Commented:
What I am doing will do EXACTLY what you want.  You should just add an identity column to #tblItems.  I can fix it with row_number(), but since you have a temp table you can add the identity value (if it doesn't exist) which is sequential (1,2,3,4....) based upon the increasing value of period.

Commented:
BrandonGalderisi:, thanks. I apologize for my miscommunication. When I was referring to as sequential, etc... is that the periods were sequential, in that the period is from month to month. However, I see your point and have added the ident column as suggested. On small test, it appears to be working, now.

I will test on larger scale and report back.

Thank you very much. Has been very educational.

Commented:
No problem.  I should have seen from your data sample that it wasn't sequential but that's my mistake!

Commented:
For my real world tests, I had to set the recursion limit on the CTE like so:
option (maxrecursion 32767)

Commented:
Thanks for your assistence. Not only did the solution work for my needs, but, I learned something new today. Is very much appreciated.
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile