?
Solved

SQL 2005 Running total with percent

Posted on 2009-02-13
26
Medium Priority
?
292 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..

0
Comment
Question by:Finkman
  • 12
  • 10
  • 2
  • +2
26 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23636082
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23636095
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

0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 23636107
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 25

Expert Comment

by:reb73
ID: 23636151
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

0
 

Author Comment

by:Finkman
ID: 23636922
BrandonGalderisi, the PERIOD will always be sequential and increasing. Thx.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23636944
Ok.  Mine should handle it even if it isn't though since I added in the row number.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 23636996
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

0
 

Author Comment

by:Finkman
ID: 23637091
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23637160
Ok... Let me fix that :(
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23637172
Is period datetime?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23637204
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

0
 

Author Comment

by:Finkman
ID: 23637206
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.

0
 

Author Comment

by:Finkman
ID: 23637242
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23637275
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
0
 

Author Comment

by:Finkman
ID: 23637346
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.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 23637362
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

0
 

Author Comment

by:Finkman
ID: 23637567
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23637671
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.
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 2000 total points
ID: 23637680
The way to fix this would be to add an identity value into #tblItems and insert with an order by statement on period.
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 2000 total points
ID: 23637696
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
0
 

Author Comment

by:Finkman
ID: 23637704
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23637720
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.
0
 

Author Comment

by:Finkman
ID: 23637771
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.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23637777
No problem.  I should have seen from your data sample that it wasn't sequential but that's my mistake!
0
 

Author Comment

by:Finkman
ID: 23637911
For my real world tests, I had to set the recursion limit on the CTE like so:
option (maxrecursion 32767)
0
 

Author Closing Comment

by:Finkman
ID: 31546724
Thanks for your assistence. Not only did the solution work for my needs, but, I learned something new today. Is very much appreciated.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question