CODEWITHSUNDEEP
mysql
dames
dames

Reputation: 1481

showing previous and current month data in table using mysql

I am trying to show three different figures of the same column In a mysql query, I would like to keep one month static: April, so it would be a case like this I want to show The current month, the previous month and the static month of the year I'm working with, in this case let us stick with 2012

Example
Tablename:payment

   id ,  pay_date,    amount
    1    2012-02-12    1000
    2    2012-03-11    780
    3    2012-04-15    890
    4    2012-05-12    1200
    5    2012-06-12    1890
    6    2012-07-12    1350
    7    2012-08-12    1450

So what I want to do is show the column amount for the month of April as I said I want to keep that row static: 890, the current month lets say the current month is August:1450 and the previous month amount which would be July:1350: so the final result would be something like this:

april_amount   current_month_amount  previous_month_amount
     890                 1450                 1350

However I'm stuck here:

select amount as april_amount
from payment
where monthname(pay_date) LIKE 'April'
and year(pay_date) LIKE 2012

I hope the question is written clear enough, and thanks alot for the help much appreciated.

Upvotes: 0

Views: 1139

Answers (2)

Milo LaMar
Milo LaMar

Reputation: 2256

I might be way off here. But try:

select top 1 
  p.amount, c.amount, n.amount 
from payment c
  inner join payment p ON p.pay_date < c.pay_date
  inner join payment n ON n.pay_date > c.pay_date
where monthname(c.paydate) LIKE 'April'
  and year(c.pay_date) LIKE 2012
order by p.pay_date DESC, n.pay_date ASC

EDIT, I didnt read your question properly. I was going for previous, current, and next month. 1 minute and I'll try again.

select top 1 
  p.amount AS april_amount, c.amount AS current_month_amount, n.amount AS previous_month_amount
from payment c
  inner join payment p ON monthname(p.pay_date) = 'April' AND year(p.pay_date) = 2012
  inner join payment n ON n.pay_date > c.pay_date
where monthname(c.paydate) = monthname(curdate())
  and year(c.pay_date) = year(curdate())
order by n.pay_date ASC

This assumes there is only 1 entry per month.

Ok, so i haven't written in mysql for a while. here is what worked for your example data:

select
  p.amount AS april_amount, c.amount AS current_month_amount, n.amount AS previous_month_amount
from payment AS c
  inner join payment AS p ON monthname(p.pay_date) LIKE 'April' AND year(p.pay_date) LIKE 2012
  inner join payment AS n ON n.pay_date < c.pay_date
where monthname(c.pay_date) LIKE monthname(curdate())
  and year(c.pay_date) LIKE year(curdate())
order by n.pay_date DESC
limit 1

the previous month table joined is counterintuitively named n, but this works. I verified it in a WAMP install.

To handle aggregates per month you can use subselects. Performance may suffer on very large tables (millions of rows or more).

SELECT SUM( a.amount ) AS april_amount, 
 (
  SELECT SUM( c.amount ) 
  FROM payment c
  WHERE MONTH( c.pay_date ) = MONTH( CURDATE( ) )
 ) AS current_month_amount, 
 (
  SELECT SUM( p.amount ) 
  FROM payment p
  WHERE MONTH( p.pay_date ) = MONTH( CURDATE( ) - INTERVAL 1 
  MONTH )
 ) AS previous_month_amount
FROM payment a
WHERE MONTHNAME( a.pay_date ) =  'April'
AND YEAR( a.pay_date ) =2012

Upvotes: 0

eggyal
eggyal

Reputation: 125855

If the results can be rows instead of columns:

SELECT MONTHNAME(pay_date), amount FROM payment

WHERE  pay_date BETWEEN '2012-04-01'
                    AND '2012-04-30'

    OR pay_date BETWEEN CURRENT_DATE
                      - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                    AND LAST_DAY(CURRENT_DATE)

    OR pay_date BETWEEN CURRENT_DATE
                      - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                      - INTERVAL 1 MONTH
                    AND LAST_DAY(CURRENT_DATE - INTERVAL 1 MONTH)

See it on sqlfiddle.

Upvotes: 1

Related Questions