I need to get results month by month for the last 3 years. This is what I have:
CREATE DEFINER=`root`@`localhost` PROCEDURE `by_year`()
DROP TEMPORARY TABLE IF EXISTS aaa;
SET @year = 2014;
WHILE @year <= YEAR(CURDATE()) DO
CREATE TEMPORARY TABLE IF NOT EXISTS aaa (Año int, Registrados int, Activos int, Compraron int, Recurrentes int);
INSERT INTO aaa
SELECT @year, registrados.count, activos.count, compraron.count, recurrentes.count FROM
# Here is my query to get the data. With a WHERE that limits the creation date by @year. [YEAR(AU.created) <= @year] For example
SELECT * FROM aaa;
It works for years. But I need to get the result by months or even weeks and days.
My problem is if I replace the YEAR function with MONTH or DAY These functions will return a number taken from the date literally (relative to the bigger divider)
For example from
So MONTH('2015-11-25') is equal to MONTH('2016-11-02')
I see two ways... Either I find a function or something like:
That similarly to dates handled by TIME, returns the month number in general in this case from year 1 that should be somenthing like (2015 * 12) + 11 = 24191
The other way could be playing around with double WHILE. And then for days triple WHILE. This will produce a complexity of O(n^3) which is not good.
Any ideas are very welcome.