Maliki Hassani
asked on
Query: annualized growth rate percentage
Hello Experts,
I am not sure how to go about building query for an annual growth rate percentage.
I have 2 devices that I would need to have a growth rate for each. Below is the example. The data only start from 2013-09 and will continue to add months.
Any ideas on how I can build my query? 1st I am not sure how it should be calculated, then I would need to write it in SQL.
Oracle 8
Thanks!
I am not sure how to go about building query for an annual growth rate percentage.
I have 2 devices that I would need to have a growth rate for each. Below is the example. The data only start from 2013-09 and will continue to add months.
Any ideas on how I can build my query? 1st I am not sure how it should be calculated, then I would need to write it in SQL.
yr_mo model_name inservice _totals
2013-09 ADB 4731 500
2013-10 ADB 4731 800
2013-11 ADB 4731 1000
2013-12 ADB 4731 1100
2014-01 ADB 4731 1150
2014-02 ADB 4731 1200
2014-03 ADB 4731 1230
2013-09 CGB 1425 200
2013-10 CGB 1425 300
2013-11 CGB 1425 400
2013-12 CGB 1425 1000
2014-01 CGB 1425 1200
2014-02 CGB 1425 1240
2014-03 CGB 1425 1300
Oracle 8
Thanks!
can you explain in layman terms what "formula" you would expect, respectively what result(s) you want to get?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Device ADB 4731
(New-old)/old * 100
(1230-500) ÷ 500 × 100 = 146%
I see there are other ways of calculating growrh rate but i think this is the most simple.
(New-old)/old * 100
(1230-500) ÷ 500 × 100 = 146%
I see there are other ways of calculating growrh rate but i think this is the most simple.
ASKER
Awking. Thank you I will review and see if i have any questions.
ASKER
my apologies.. i forgot I am using MySQL server. any way you can convert that to. mysql. iYou did answer the question for me though, regardless.
ASKER
> Script lines: 1-5 --------------------------
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'over (partition by model_name order by yr_mo))/
lag(inservice_totals) over (part' at line 2
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'over (partition by model_name order by yr_mo))/
lag(inservice_totals) over (part' at line 2
Too bad that MySQL doesn't support any analytic functions. You could perhaps use a self join (i.e using aliases t1 and t2 for yourtable) where the model_names are equal and the yr_mo is different by one, then join this to a subquery (aliased as t3) that gets the minimum inservice_totals (i.e. select model_name, min(inservice_totals) as mintot group by model_name) to get the starting inservice_totals value. The monthly percentage growth could then determined by (t2.inservice_totals - t1.inservice_totals)/t1.in service_to tals * 100 and the ytd percentage growth could then be detmined by (t1.inservice_totals - t3.mintot)/t3.mintot * 100. This would, of course, require some manipulation of the yr_mo so the math could be done to determine the difference of 1.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you all! Sorry for the confusion in the begining. I appreciate the help! I learned several things...