I have one table (but at many locations):
DATE STUFF
-------------------
2011-12-01 DATA
2011-12-02 DATA
2011-12-03 DATA
...
2011-12-31 DATA
2012-01-01 DATA
2012-01-02 DATA
My table covers multiple years from 2005 to 2012. I want to get AGGREGATE Function values, i.e., SUM/AVG/MAX/MIN, for each month within each year. Easy:
GROUP BY DATE_FORMAT(DATE, '%Y'), DATE_FORMAT(DATE, '%m')
I want go do the same for 3-month time periods within those years... this works for all but one:
GROUP BY DATE_FORMAT(DATE, '%Y'), DATE_FORMAT(DATE, '%m') IN (12, 01, 02)
My other time periods work, because they are in the same year (03, 04, 05), (06, 07, 08), and (09, 10, 11)... but the GROUP BY above is grouping December of 2012 with January/February of 2012. My time period has to be December 2011 and January/February 2012, December 2010 and January/February 2011... ...
I want to keep this generic, so I don't have to update with date spans, in order to put the code in a stored procedure for multiple locations.
I've tried to join the table to it self by shifting the year ahead by one if MONTH 12. This yielded undesirable results.