Alejandro - 10 months ago 53

MySQL Question

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`()`

BEGIN

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

END WHILE;

SELECT * FROM aaa;

END

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

`MONTH('2015-11-25')`

I get

`11`

So MONTH('2015-11-25') is equal to MONTH('2016-11-02')

I see two ways... Either I find a function or something like:

`MONTH_FROM_THE_BEGGINING_OF_TIMES('2016-11-02')`

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.

Thank you

Answer

Source (Stackoverflow)