Generate all months dynamically between two dates on a table

Question

I have a table like this:

NAME	INT_VALUE	START	END
One	100	2013-11-16	2014-11-16

The goal is to output like this:

NAME	INT_VALUE	START	END	INTERVAL
One	100	2013-11-16	2014-11-16	11-2013
One	100	2013-11-16	2014-11-16	12-2013
One	100	2013-11-16	2014-11-16	1-2014
One	100	2013-11-16	2014-11-16	2-2014

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.

Answer 1

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