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

Medium Priority
323 Views
Last Modified: 2012-06-27
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

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

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

Open in new window

Kyle AbrahamsSenior .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

Open in new window

Author

Commented:
BrandonGalderisi, the PERIOD will always be sequential and increasing. Thx.
Ok.  Mine should handle it even if it isn't though since I added in the row number.
SharathData 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

Open in new window

Author

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.
Ok... Let me fix that :(
Is period datetime?
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

Open in new window

Author

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.

Author

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.
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

dateadd(d, datediff(d, 0, a.period),0)  
and
dateadd(d, datediff(d, 0, period),0)  


respectively

Author

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.
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

Open in new window

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

Ask the Experts

Author

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.
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.
The way to fix this would be to add an identity value into #tblItems and insert with an order by statement on period.
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

Author

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.
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.

Author

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.
No problem.  I should have seen from your data sample that it wasn't sequential but that's my mistake!

Author

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

Author

Commented:
Thanks for your assistence. Not only did the solution work for my needs, but, I learned something new today. Is very much appreciated.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.