Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

mySQL: Date change for previous month

Posted on 2014-08-12
15
Medium Priority
?
386 Views
Last Modified: 2014-08-13
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

0
Comment
Question by:Maliki Hassani
  • 8
  • 3
  • 2
  • +1
15 Comments
 
LVL 16

Assisted Solution

by:hankknight
hankknight earned 1000 total points
ID: 40256513
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
 

Author Comment

by:Maliki Hassani
ID: 40256616
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
 
LVL 16

Expert Comment

by:hankknight
ID: 40256632
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40257050
0
 

Author Comment

by:Maliki Hassani
ID: 40257093
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
 

Author Comment

by:Maliki Hassani
ID: 40257099
Its not a date field, so I think it could work. The month is always 2 digits, 07, 08, 10, etc
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40257335
Its not a date field...
Good grief, why not??
0
 

Author Comment

by:Maliki Hassani
ID: 40257350
Its a data dump that we get each month.  Having no date timestamp.  Yeah i know
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1000 total points
ID: 40257451
|       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
 

Author Comment

by:Maliki Hassani
ID: 40257461
Perfect... Thank you so much.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40257478
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
 

Author Comment

by:Maliki Hassani
ID: 40258124
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
 

Author Comment

by:Maliki Hassani
ID: 40258141
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40258158
Yes! you are quite right! the movement of date has to affect both year and month - mea culpa

that looks right
0
 

Author Comment

by:Maliki Hassani
ID: 40258178
thank you!
0

Featured Post

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.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

580 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