Solved

Query:  annualized growth rate percentage

Posted on 2014-04-12
12
402 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Update query with connected table data 3 63
SQL Recursion schedule 13 35
Finding Where Clause Value in SQL Views and SP 21 43
calculate days away 11 29
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 …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

726 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