DELIMITER |

DROP PROCEDURE IF EXISTS cppm|
# COUNT patients per MONTH (5 LAST months)
CREATE PROCEDURE cppm (OUT ototal INT)
BEGIN
    DECLARE i INT DEFAULT 5;
    DECLARE items TABLE(smonth DATE, total INT);
    DECLARE sdate DATE DEFAULT DATE_FORMAT(CURRENT_DATE(), '%Y-%c-01');
    WHILE i > 0 DO

        SET i = i - 1;
        SET sdate = DATE_SUB(sdate, INTERVAL 1 MONTH);
        SELECT DISTINCT COUNT(Users.id) INTO RESULT
        FROM gestcab_users AS Users
        LEFT JOIN gestcab_roles AS Roles ON Users.role_id = Roles.id
        WHERE Roles.slug = 'patient'
        AND DATE(Users.created) < sdate AND
        DATE(Users.created) >= DATE_SUB(sdate, INTERVAL 1 MONTH);
        INSERT INTO items(smonth, total) VALUES(sdate, RESULT);

    END WHILE;

    SELECT SUM(total)  FROM items INTO ototal;
END|

CALL cppm()|
# SELECT @total|