make_months_table

.sql

-- Gets the start and end dates of all months going back 18 months.

/* DROP TABLE IF EXISTS month_table; # For debugging
CREATE TABLE month_table
(
    m_id       INT,
    month_name VARCHAR(20),
    start_date DATETIME,
    end_date   DATETIME
); */


DELIMITER  $$
DROP PROCEDURE IF MAKE_MONTHS_TABLES;
CREATE PROCEDURE MAKE_MONTHS_TABLE()
BEGIN
    DECLARE month_max INT UNSIGNED DEFAULT 18;
    DECLARE month_counter INT UNSIGNED DEFAULT 1;
    DECLARE date DATETIME DEFAULT NOW();
    WHILE month_counter <= month_max
        DO
            REPLACE INTO month_table (m_id, month_name, start_date, end_date)
            VALUES (month_counter, MONTHNAME(DATE_SUB(date, INTERVAL month_counter MONTH)),
                    DATE_ADD(DATE_ADD(last_day(date), INTERVAL 1 day), INTERVAL -(month_counter + 1) MONTH),
                    LAST_DAY(DATE_SUB(date, INTERVAL month_counter MONTH)));
            SET month_counter = month_counter + 1;
        END WHILE;
    COMMIT;
END $$

CALL MAKE_MONTHS_TABLE();