Solved

Query:  annualized growth rate percentage

Posted on 2014-04-12
12
385 Views
Last Modified: 2014-04-13
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.

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

Open in new window


Oracle 8

Thanks!
0
Comment
Question by:Maliki Hassani
  • 5
  • 2
  • 2
  • +1
12 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39996210
can you explain in layman terms what "formula" you would expect, respectively what result(s) you want to get?
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 166 total points
ID: 39996286
I'm not exactly sure what you're looking for, but the following would show the percentage change from month to month and year-to-date for each model_name:
select yr_mo, model_name, inservice_totals,
round((inservice_totals - lag(inservice_totals) over (partition by model_name order by yr_mo))/
lag(inservice_totals) over (partition by model_name order by yr_mo) * 100,2) mthly_growth,
round((inservice_totals - first_value(inservice_totals) over (partition by model_name order by yr_mo))/first_value(inservice_totals) over (partition by model_name order by yr_mo) * 100,2) ytd_growth
from yourtable;
0
 

Author Comment

by:Maliki Hassani
ID: 39996295
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.
0
 

Author Comment

by:Maliki Hassani
ID: 39996303
Awking.  Thank you I will review and see if i have any questions.
0
 

Author Comment

by:Maliki Hassani
ID: 39996361
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.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Maliki Hassani
ID: 39996369
> 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
0
 
LVL 32

Expert Comment

by:awking00
ID: 39996405
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.inservice_totals * 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.
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 167 total points
ID: 39996461
after seeing awking comment, analytical functions will not work in mysql, tried out a bit in sql for one model and got the result using dual table..

also found out the if we remove " from dual" word from the below query,. this should work in mysql too..

. not sure if this could help.. had given it a try though

SELECT   (  (SELECT inservice_totals
               FROM tab1 t
              WHERE TO_CHAR (TO_DATE (yr_mo, 'YYYY MM'), 'YYYY MM') IN (
                       SELECT TO_CHAR (MAX (TO_DATE (yr_mo, 'YYYY MM')),
                                       'YYYY MM'
                                      )
                         FROM tab1)
                AND model_name = 'ADB 1')
          - (SELECT inservice_totals
               FROM tab1 t
              WHERE TO_CHAR (TO_DATE (yr_mo, 'YYYY MM'), 'YYYY MM') IN (
                       SELECT TO_CHAR (MIN (TO_DATE (yr_mo, 'YYYY MM')),
                                       'YYYY MM'
                                      )
                         FROM tab1)
                AND model_name = 'ADB 1')
         )
       / (SELECT inservice_totals
            FROM tab1 t
           WHERE TO_CHAR (TO_DATE (yr_mo, 'YYYY MM'), 'YYYY MM') IN (
                    SELECT TO_CHAR (MIN (TO_DATE (yr_mo, 'YYYY MM')),
                                    'YYYY MM'
                                   )
                      FROM tab1)
             AND model_name = 'ADB 1') AS diff
  FROM DUAL
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 167 total points
ID: 39996483
in mysql, I would try this (untested):
select t.model_name
  , max(case when t.yr_mo = g.min_yr_mo then t._totals end) min_total
  , max(case when t.yr_mo = g.max_yr_mo then t_totals end) max_total
  , 100 * ( max(case when t.yr_mo = g.max_yr_mo then t_totals end) - max(case when t.yr_mo = g.min_yr_mo then t._totals end)) / 
  max(case when t.yr_mo = g.min_yr_mo then t_totals end) annual_growth
from (
select min(yr_mo) min_yr_mo
  , max(yr_mo) max_yr_mo
  , model_name
 from yourtable
 group by model_name
 ) g
join yourtable t
  on t.model_name  = g.model_name
group by  t.model_name 

Open in new window

0
 

Author Closing Comment

by:Maliki Hassani
ID: 39996516
thank you all!  Sorry for the confusion in the begining.  I appreciate the help!  I learned several things...
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now