Solved

Query:  annualized growth rate percentage

Posted on 2014-04-12
12
383 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 31

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 31

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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 …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

18 Experts available now in Live!

Get 1:1 Help Now