I have a table like this:

The goal is to output like this:

I've started using this. But I am not really sure how I'd use the START and END dates on the table to determine the start and end to calculate the list of months between.

SELECT START, calendar, COUNT(1)
FROM table1 AS t1
    RIGHT JOIN (SELECT row_number() OVER (ORDER BY SEQ4()) AS MONTHS
                ,      TO_DATE(DATEADD(MONTH, MONTHS, '2019-05-01')) AS calendar
                FROM TABLE(GENERATOR(rowcount=>80)))
            ON t1.START = calendar
GROUP BY 1, 2
ORDER BY 2, 1
;

The goal here is to associate the int value with every month from the beginning to the end of the range.

CodePudding user response：

WITH RECURSIVE
cte AS ( SELECT name, int_value, start, `end`, 
                1 rownum, DATE_FORMAT(start, '%m-%Y') `interval`
         FROM source_table
         UNION ALL
         SELECT name, int_value, start, `end`, 
                1   rownum, DATE_FORMAT(start   INTERVAL rownum MONTH, '%m-%Y')
         FROM cte
         WHERE start   INTERVAL rownum - 1 MONTH < `end` )
         
SELECT name, int_value, start, `end`, `interval`
FROM cte
ORDER BY rownum;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=bdd028a7755fdcb8296df2301baeb295

If you do not need in leading zeros in month numbers then use '%c-%Y' pattern.

CodePudding user response：

so moving the range of numbers out to it's own CTE to separate things, we now have a big list of 80 numbers (it could be bigger).

Then we find the number of months between the start/end and join to that many rows. And do the math to turn the range into the select part:

WITH range_of_numbers AS (
    SELECT 
        row_number() OVER (ORDER BY SEQ4())-1 AS rn
    FROM TABLE(GENERATOR(rowcount=>80))
)
SELECT 
    t1.name, 
    t1.int_value, 
    t1.start,
    t1.end,
    DATEADD(MONTH, r.rn, t1.start) as interval
FROM table1 AS t1
JOIN range_of_numbers as r
    ON date_diff('month', t1.START, t1.end) <= between r.rn
ORDER BY 2,1,3;

Another option is it build a long range DATE table

CREATE TABLE dates AS 
SELECT 
     DATEADD(MONTH, row_number() OVER (ORDER BY SEQ4())-1, '1980-01-01') as month_date
FROM TABLE(GENERATOR(rowcount=>8000))

Then we use BETWEEN to get the inclusive values in the (start,end) range would become:

FROM table1 AS t1
JOIN dates as d
    ON d.month_date BETWEEN t1.START AND t1.end