Get values for last 6 months in mysql
I need to get the last six months from the database. Here is my Fiddle . I need to get the values dynamically. Now in February. So I need from August to January the values from the database. If in March the values will automatically change from October to February. So I need to get the last six months.
My code follows.
CREATE TABLE IF NOT EXISTS `ratepersqft` (
`price_id` int(11) NOT NULL auto_increment,
`project_id` int(11) default NULL,
`date` varchar(255) default NULL,
`rate_per_sqft` double default NULL,
`common_location` varchar(255) default NULL,
PRIMARY KEY (`price_id`)
);
INSERT INTO `ratepersqft` (`price_id`, `project_id`, `date`, `rate_per_sqft`, `common_location`) VALUES
(1, 1, '2012-07-01 ', 2800, 'ECR'),
(2, 2, '2012-07-02 ', 1550, 'ECR'),
(3, 3, '2012-07-03 ', 1850, 'ECR'),
(4, 4, '2012-07-04 ', 4425, 'ECR'),
(5, 5, '2012-07-05 ', 2300, 'ECR'),
(6, 1, '2012-08-01 ', 2900, 'ECR'),
(7, 2, '2012-08-02 ', 1650, 'ECR'),
(8, 3, '2012-08-03 ', 1950, 'ECR'),
(9, 4, '2012-08-04 ', 4525, 'ECR'),
(10, 5, '2012-08-05 ', 2200, 'ECR'),
(11, 1, '2012-09-01 ', 3000, 'ECR'),
(12, 2, '2012-09-02 ', 1450, 'ECR'),
(13, 3, '2012-09-03 ', 2050, 'ECR'),
(14, 4, '2012-09-04 ', 4625, 'ECR'),
(15, 5, '2012-09-05 ', 2100, 'ECR'),
(16, 1, '2012-10-01 ', 3100, 'ECR'),
(17, 2, '2012-10-02 ', 2150, 'ECR'),
(18, 3, '2012-10-03 ', 1850, 'ECR'),
(19, 4, '2012-10-04 ', 4725, 'ECR'),
(20, 5, '2012-10-05 ', 1900, 'ECR'),
(21, 1, '2012-11-01 ', 3200, 'ECR'),
(22, 2, '2012-11-02 ', 2250, 'ECR'),
(23, 3, '2012-11-03 ', 1850, 'ECR'),
(24, 4, '2012-11-04 ', 4825, 'ECR'),
(25, 5, '2012-11-05 ', 2300, 'ECR'),
(26, 1, '2012-12-01 ', 3300, 'ECR'),
(27, 2, '2012-12-02 ', 2350, 'ECR'),
(28, 3, '2012-12-03 ', 1850, 'ECR'),
(29, 4, '2012-12-04 ', 4925, 'ECR'),
(30, 5, '2012-12-05 ', 2400, 'ECR'),
(31, 1, '2013-01-01 ', 3400, 'ECR'),
(32, 2, '2013-01-02 ', 2000, 'ECR'),
(33, 3, '2013-01-03 ', 2450, 'ECR'),
(34, 4, '2013-01-04 ', 5025, 'ECR'),
(35, 5, '2013-01-05 ', 2500, 'ECR'),
(36, 1, '2013-02-01 ', 3500, 'ECR'),
(37, 2, '2013-02-02 ', 2100, 'ECR'),
(38, 3, '2013-02-03 ', 2550, 'ECR'),
(39, 4, '2013-02-04 ', 5125, 'ECR'),
(40, 5, '2013-02-05 ', 2600, 'ECR'),
(41, 1, '2012-06-01 ', 1800, 'ECR'),
(42, 2, '2012-06-02 ', 1150, 'ECR'),
(43, 3, '2012-06-03 ', 1350, 'ECR'),
(44, 4, '2012-06-04 ', 3425, 'ECR'),
(45, 5, '2012-06-05 ', 2100, 'ECR');
I am trying this query and it gives me all the values in the database.
SELECT *
FROM ratepersqft
WHERE MONTH(date) < DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
Hope my question is clear. Thanks in Advance !!!
source to share
For MYSQL: you can use date_add
:
SELECT * FROM ratepersqft
WHERE date < Now() and date > DATE_ADD(Now(), INTERVAL- 6 MONTH);
For SQL Server :, dateadd
:
SELECT * FROM ratepersqft
WHERE date < Now() and date > DATEADD(Month, -6, Now());
http://www.sqlfiddle.com/#!2/1f8029/48
** Please wrap the countdown date column name if this is a reserved keyword. **
MYSQL update:
SELECT *
FROM ratepersqft
WHERE date_format(date,'%Y-%m') <
date_format(now(),'%Y-%m')
and date_format(date,'%Y-%m') >=
date_format(now() - interval 6 month,'%Y-%m')
order by date desc;
source to share
This will consider all dates rounded to the start of the month for comparisons, and will get the last 6 months from today:
SELECT *
FROM ratepersqft
WHERE date >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 6 MONTH)
AND date < DATE_FORMAT(CURDATE(), '%Y-%m-01')
Required SQLFiddle .
source to share
SELECT
YEAR(DATE) AS YEAR,
SUM(MONTH(DATE) = 1) AS JAN ,
SUM(MONTH(DATE) = 2) AS FEB ,
SUM(MONTH(DATE) = 3) AS MAR ,
SUM(MONTH(DATE) = 4) AS APR,
SUM(MONTH(DATE) = 5) AS MAY ,
SUM(MONTH(DATE) = 6) AS JUNE ,
SUM(MONTH(DATE) = 7) AS JULY ,
SUM(MONTH(DATE) = 8) AS AUG,
SUM(MONTH(DATE) = 9) AS SEP ,
SUM(MONTH(DATE) = 10) AS OCTOBER ,
SUM(MONTH(DATE) = 11) AS NOV ,
SUM(MONTH(DATE) = 12) AS DECE
FROM ratepersqft
WHERE DATE >= NOW() - INTERVAL 6 MONTH
GROUP BY 1;