Solved

Query:  annualized growth rate percentage

Posted on 2014-04-12
12
388 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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
 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Crosstab Query with Multiple Values 4 32
mysql date time 14 30
Query Help - MSSQL - Averages 5 27
Find results from sql within a time span 11 32
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

777 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