Solved

Query:  annualized growth rate percentage

Posted on 2014-04-12
12
393 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 143

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 143

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Run SQL Server Proc from Access 11 37
SQL Server 2012 r2 - Varible Table 3 31
SQL Syntax 24 42
SQL Query Syntax to add subtotal calculation by Contractor for each row. 3 23
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 …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

791 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