Mir Abzal Ali Mir Abzal Ali - 12 days ago 8
MySQL Question

How to use a set of data to generate date list between two date for each ID in mysql

Here's the table data for each

ID
and I need to pass this as parameter and generate date list for each id and insert into table.

ID startdate enddate
1 2016-01-01 2020-12-31
2 2017-01-01 2020-12-31
3 2018-01-01 2020-12-31
4 2019-01-01 2020-12-31
5 2020-01-01 2020-12-31


this stored procedure run for a single value via parameter:

DELIMITER @@
DROP PROCEDURE make_intervals @@
CREATE PROCEDURE vtproject.make_intervals
(IN `startdate` DATE, IN `enddate` DATE)
BEGIN
declare thisDate DATE;
declare nextDate DATE;
declare intval integer;
declare unitval varchar(10);

set thisDate = startdate;
set intval = 1;
set unitval = 'YEAR';

drop temporary table if exists year_intervals;
create temporary table if not exists year_intervals
(
interval_start DATE,
interval_end DATE
);

repeat
select
case unitval
when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
when 'SECOND' then timestampadd(SECOND, intval, thisDate)
when 'MINUTE' then timestampadd(MINUTE, intval, thisDate)
when 'HOUR' then timestampadd(HOUR, intval, thisDate)
when 'DAY' then timestampadd(DAY, intval, thisDate)
when 'WEEK' then timestampadd(WEEK, intval, thisDate)
when 'MONTH' then timestampadd(MONTH, intval, thisDate)
when 'QUARTER' then timestampadd(QUARTER, intval, thisDate)
when 'YEAR' then timestampadd(YEAR, intval, thisDate)
end into nextDate;

insert into year_intervals select thisDate, dateadd(DAY, -1, nextDate);
set thisDate = nextDate;
until thisDate >= enddate
end repeat;
select * from year_intervals;
END @@
DELIMITER ;

Answer

Two versions below. The first does it thru a single pair of dates. The second version processes the whole table at once via a cursor.

Version 1:

DROP PROCEDURE if exists make_intervals;
DELIMITER @@
CREATE PROCEDURE make_intervals
(IN `startdate` DATE, IN `enddate` DATE)
BEGIN
    declare thisDate DATE;
    declare nextDate DATE;
    declare intval integer;
    declare unitval varchar(10);

    set thisDate = startdate;
    set intval = 1;
    set unitval = 'YEAR';

    drop temporary table if exists year_intervals;
    create temporary table if not exists year_intervals
      (
      interval_start DATE,
      interval_end DATE
      );

    repeat
      select
         case unitval
            when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
            when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
            when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
            when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
            when 'DAY'         then timestampadd(DAY, intval, thisDate)
            when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
            when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
            when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
            when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
         end into nextDate;

      insert into year_intervals select thisDate, date_add(nextDate,INTERVAL -1 DAY);
      set thisDate = nextDate;
    until thisDate >= enddate
    end repeat;
    select * from year_intervals;
END @@ 
DELIMITER ; 

test:

call make_intervals('2016-01-01','2020-12-31');
+----------------+--------------+
| interval_start | interval_end |
+----------------+--------------+
| 2016-01-01     | 2016-12-31   |
| 2017-01-01     | 2017-12-31   |
| 2018-01-01     | 2018-12-31   |
| 2019-01-01     | 2019-12-31   |
| 2020-01-01     | 2020-12-31   |
+----------------+--------------+

Version 2 (reworked with 2 different parameters, entire table):

create table myT
(   ID INT auto_increment primary key,
    startdate DATE NOT NULL,
    enddate DATE NOT NULL
);
INSERT myT(startdate,enddate) values
('2016-01-01','2020-12-31'),
('2017-01-01','2020-12-31'),
('2018-01-01','2020-12-31'),
('2019-01-01','2020-12-31'),
('2020-01-01','2020-12-31');

DROP PROCEDURE if exists make_intervals_v2;
DELIMITER @@
CREATE PROCEDURE make_intervals_v2
(   IN unitval VARCHAR(10),
    IN intval INT
)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE thisDate,nextDate DATE;
    -- declare intval integer;
    DECLARE l_ID integer;
    DECLARE l_startdate,l_enddate DATE;
    DECLARE theCursor CURSOR FOR SELECT ID,startdate,enddate from myT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    drop temporary table if exists year_intervals2;
    create temporary table year_intervals2
    (   id int AUTO_INCREMENT primary key,
        sourceID INT NOT NULL,
        interval_start DATE NOT NULL,
        interval_end DATE NOT NULL
    );

    OPEN theCursor;
    read_loop: LOOP
        FETCH theCursor INTO l_ID,l_startdate,l_enddate;
        IF done THEN
            LEAVE read_loop;
        END IF;
        set thisDate = l_startdate;

        repeat
          select
             case unitval
                when 'MICROSECOND' then timestampadd(MICROSECOND, intval, thisDate)
                when 'SECOND'      then timestampadd(SECOND, intval, thisDate)
                when 'MINUTE'      then timestampadd(MINUTE, intval, thisDate)
                when 'HOUR'        then timestampadd(HOUR, intval, thisDate)
                when 'DAY'         then timestampadd(DAY, intval, thisDate)
                when 'WEEK'        then timestampadd(WEEK, intval, thisDate)
                when 'MONTH'       then timestampadd(MONTH, intval, thisDate)
                when 'QUARTER'     then timestampadd(QUARTER, intval, thisDate)
                when 'YEAR'        then timestampadd(YEAR, intval, thisDate)
             end into nextDate;

            insert into year_intervals2 (sourceID,interval_start,interval_end) select l_ID,thisDate, date_add(nextDate,INTERVAL -1 DAY);
          set thisDate = nextDate;
        until thisDate >= l_enddate 
        end repeat;
    END LOOP;

    select * from year_intervals2;
END @@ 
DELIMITER ; 

Test:

call make_intervals_v2('YEAR',1);
select * from year_intervals2;
+----+----------+----------------+--------------+
| id | sourceID | interval_start | interval_end |
+----+----------+----------------+--------------+
|  1 |        1 | 2016-01-01     | 2016-12-31   |
|  2 |        1 | 2017-01-01     | 2017-12-31   |
|  3 |        1 | 2018-01-01     | 2018-12-31   |
|  4 |        1 | 2019-01-01     | 2019-12-31   |
|  5 |        1 | 2020-01-01     | 2020-12-31   |
|  6 |        2 | 2017-01-01     | 2017-12-31   |
|  7 |        2 | 2018-01-01     | 2018-12-31   |
|  8 |        2 | 2019-01-01     | 2019-12-31   |
|  9 |        2 | 2020-01-01     | 2020-12-31   |
| 10 |        3 | 2018-01-01     | 2018-12-31   |
| 11 |        3 | 2019-01-01     | 2019-12-31   |
| 12 |        3 | 2020-01-01     | 2020-12-31   |
| 13 |        4 | 2019-01-01     | 2019-12-31   |
| 14 |        4 | 2020-01-01     | 2020-12-31   |
| 15 |        5 | 2020-01-01     | 2020-12-31   |
+----+----------+----------------+--------------+

Test another,

call make_intervals_v2('WEEK',2);

enter image description here

The above looping (dual looping with the cursor and the repeat / until) does not perform so stellar. I could rework it. It is just an example of what you could do.