SQL to get Compund Growth Rate 1

[MSIsam]

Does anyone have any experience writng SQL to get a compund growth rate? I would like to calculate CGR over time at any level in the table:
Examples:
single product/single customer
multiple product/single customer
single product/multiple customer
multiple product/multiple customer

The table structure would be in the following format

date     product customer sales
1/1/05   prd1    cust1    10
1/3/05   prd1    cust1    15
1/8/05   prd1    cust1    7
1/11/05  prd1    cust1    20
1/3/05   prd2    cust1    17
1/7/05   prd2    cust1    27
1/3/05   prd2    cust2    10
1/7/05   prd2    cust2    45

I am currently bringing the data into excel to do and would like to incorporate the logic to sql.

Any help would be greatly appreciated.

 

[SantaMufasa]

MS,

Sure, we can post a "COMP_INT" (Compound Interest) function for you, but first, you need to specify which Compound-Interest formula you want to use, since there are so many variations on what the formulae can calculate (e.g.,

"Single Payment - Compound Amount",
"Single Payment - Present Value",
"Sinking Fund",
"Capital Recovery",
"Uniform Series - Compound Amount"
"Uniform Series - Present Worth"
et cetera

So if you can post what "compound-interest" formula you want and what values you want to input to return your desired output, then we should be able to build what you want.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[MSIsam]

Thanks SantaMufasa,
I am not sure what kind of CGR this is but the formula I use in excel is:

FORMULA:CGR = (y/x)^(1/)-1
where ^ is the exponent indicator
y = Last value
x = first value
n = number of periods

Unfortunately it gets trickier:
I want to derive the first and last values (input x and y) from a linear trendline of the values using the intercept and slope.
last value will correspond to the trendline sales value at the maximum date
first value (x)will correspond to the trendline sales value at the minimum date

Hope this makes sense,
Sam

 

[SantaMufasa]

MS,

Here is a function "comp_int" into which I plugged your formula. I don't know if it does what you wanted, but you can certainly take this code as a sample and tweak it to do what you really want:

create or replace function comp_int (y number, x number, n number)
    return number is
begin
    return power((y/x),(1/(n))-1);
end;
/

function created.

select comp_int(100,1,48) from dual;

.011006942

Let us know if this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[MSIsam]

Thanks SantaMufasa,
That puts me on the right track!

 

[MSIsam]

I thought I would post what I came up with building on all of your help. This works great and has really been a time saver for us.

Create Test Data set:

create table A nologging as
select 'PRODUCT A' product,1 weekno,100 SALES from dual
union
select  'PRODUCT A' ,  2,200 from dual
union
select 'PRODUCT A' , 3,250 from dual
union
select 'PRODUCT A' , 4,175 from dual
union 
select 'PRODUCT A' , 5,200 from dual;

SQL to get Compound Growth Rate

select product,
REGR_SLOPE(SALES,weekno) SLOPE,
REGR_INTERCEPT(SALES,weekno) INTERCEPT,
max(weekno) MAX_X,
min(Weekno) MIN_X,
count(weekno) NBR_of_periods,
(REGR_SLOPE(SALES,weekno)*min(Weekno) )+REGR_INTERCEPT(SALES,weekno) MIN_Y,
(REGR_SLOPE(SALES,weekno)*max(Weekno) )+REGR_INTERCEPT(SALES,weekno) MAX_Y,
POWER(
      ((REGR_SLOPE(SALES,weekno)*max(Weekno))+
       REGR_INTERCEPT(SALES,weekno)
       )/
      ((REGR_SLOPE(SALES,weekno)*min(Weekno))+
       REGR_INTERCEPT(SALES,weekno)
       ) ,(1/count(weekno))
     )-1 CGR
 from a
group by product

There results match with what I get using Excel

Thanks Again,
Sam