mySQL: Date change for previous month

Hello Experts,

I have a query that should pull the previous month's data and 2 months back.  I have hardcoded my date (2014-07 and 2014-06) but would like to have a formula that would set that date range automatically.

Any ideas?

//MONTHLY
SELECT yr_mo,model_name,Previous_month,Current_month,( (Current_month - Previous_month) / Previous_month) as monthly_growth
from (
SELECT yr_mo,model_name,fla_inservice_totals as Current_month,
   (SELECT fla_inservice_totals
    FROM Table e2
    WHERE e2.model_name = e1.model_name AND e2.yr_mo < e1.yr_mo AND yr_mo = '2014-06'
    ORDER BY yr_mo DESC
    LIMIT 1) Previous_month
FROM Table e1
WHERE yr_mo = '2014-07'
)t1;

Open in new window

Maliki HassaniAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hankknightCommented:
One month ago:
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH)

Open in new window

Two moths ago:
SELECT DATE_SUB(NOW(), INTERVAL 2 MONTH)

Open in new window

So you could modify your query like this:
//MONTHLY
SELECT yr_mo,model_name,Previous_month,Current_month,( (Current_month - Previous_month) / Previous_month) as monthly_growth
from (
SELECT yr_mo,model_name,fla_inservice_totals as Current_month,
   (SELECT fla_inservice_totals
    FROM Table e2
    WHERE e2.model_name = e1.model_name AND e2.yr_mo < e1.yr_mo AND yr_mo = (SELECT DATE_SUB(NOW(), INTERVAL 2 MONTH))
    ORDER BY yr_mo DESC
    LIMIT 1) Previous_month
FROM Table e1
WHERE yr_mo = SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH)
)t1;

Open in new window

0
Maliki HassaniAuthor Commented:
Thank you, but I am getting an error:

> Script lines: 197-207 ----------------------
 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 'SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH)
)t1' at line 10
0
hankknightCommented:
The trick is to use this:
SELECT DATE_SUB(NOW(), INTERVAL 2 MONTH)

Open in new window

If you have a background forming SQL queries you should be able to make your query work with that.  If you want further help, please provide the database structure in the form of an SQL dump or in a link to SQL Fiddle.  Otherwise I am shooting in the dark.

http://sqlfiddle.com/
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Ray PaseurCommented:
0
Maliki HassaniAuthor Commented:
Thanks..  i think the issue is that it needs to be in the format like 2014-07.  If i could breakout the year and month number it would work.
0
Maliki HassaniAuthor Commented:
Its not a date field, so I think it could work. The month is always 2 digits, 07, 08, 10, etc
0
Ray PaseurCommented:
Its not a date field...
Good grief, why not??
0
Maliki HassaniAuthor Commented:
Its a data dump that we get each month.  Having no date timestamp.  Yeah i know
0
PortletPaulfreelancerCommented:
|       N |     N_2 |     N_1 |
|---------|---------|---------|
| 2014-08 | 2014-06 | 2014-07 |

select
  concat(year(now()),'-',right(concat('0', month(now()) ),2)) as n
, concat(year(now()),'-',right(concat('0', month(date_sub(now(), INTERVAL 2 MONTH)) ),2)) as n_2
, concat(year(now()),'-',right(concat('0', month(date_sub(now(), INTERVAL 1 MONTH)) ),2)) as n_1

Open in new window

Tedious but it works. For your query then it would be:
--MONTHLY
SELECT
      yr_mo
    , model_name
    , Previous_month
    , Current_month
    , ((Current_month - Previous_month) / Previous_month) AS monthly_growth
FROM (
            SELECT
                  e1.yr_mo
                , e1.model_name
                , e1.fla_inservice_totals AS Current_month
                , (
                        SELECT
                              e2.fla_inservice_totals
                        FROM TableX e2
                        WHERE e2.model_name = e1.model_name
                              AND e2.yr_mo < e1.yr_mo
                              AND yr_mo = concat(year(now()),'-',right(concat('0', month(date_sub(now(), INTERVAL 2 MONTH)) ),2))
                        ORDER BY
                              yr_mo DESC
                        LIMIT 1
                  )                       
                  Previous_month
            FROM TableX e1
            WHERE e1.yr_mo = concat(year(now()),'-',right(concat('0', month(date_sub(now(), INTERVAL 1 MONTH)) ),2))
      ) t1;

Open in new window

OR if you prefer variables:
--MONTHLY
set @mnth_minus2 := concat(year(now()),'-',right(concat('0', month(date_sub(now(), INTERVAL 2 MONTH)) ),2));
set @mnth_minus1 := concat(year(now()),'-',right(concat('0', month(date_sub(now(), INTERVAL 1 MONTH)) ),2));

SELECT
      yr_mo
    , model_name
    , Previous_month
    , Current_month
    , ((Current_month - Previous_month) / Previous_month) AS monthly_growth
FROM (
            SELECT
                  e1.yr_mo
                , e1.model_name
                , e1.fla_inservice_totals AS Current_month
                , (
                        SELECT
                              e2.fla_inservice_totals
                        FROM TableX e2
                        WHERE e2.model_name = e1.model_name
                              AND e2.yr_mo < e1.yr_mo
                              AND yr_mo = @mnth_minus2
                        ORDER BY
                              yr_mo DESC
                        LIMIT 1
                  )                       
                  Previous_month
            FROM TableX e1
            WHERE e1.yr_mo = @mnth_minus1
      ) t1;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Maliki HassaniAuthor Commented:
Perfect... Thank you so much.
0
PortletPaulfreelancerCommented:
A pleasure, thank you. Cheers, Paul

btw: I toyed with the idea of replacing the correlated subquery. If the performance of this query is a problem then maybe using a left join instead would help - but it won't be simple to do and maybe not worth it if performance of this one is acceptable (and it would be a new question)

{+ edit, sorry}
I should add "and no guarantee it will perform better" to the above
0
Maliki HassaniAuthor Commented:
I have one last question about the year.  What if I run this on January 2015, would the year function give me the previous year? so it should change to 2014-12 and 2014-11.
0
Maliki HassaniAuthor Commented:
Would this work?

concat((year(date_sub(now(), INTERVAL 2 MONTH))),'-',right(concat('0', month(date_sub(now(), INTERVAL 2 MONTH)) ),2))

Open in new window

0
PortletPaulfreelancerCommented:
Yes! you are quite right! the movement of date has to affect both year and month - mea culpa

that looks right
0
Maliki HassaniAuthor Commented:
thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.