Generate rows by month for the month between Start Date and End Dates in Redshift

If you run the first only SELECT part which is for the sample data set you will see below dataset.


Sample Data Set :-

item_id	start_date	end_date	monthly_revenue
242345	2/26/2016	7/26/2016	700

Desired Output by month :-

item_id	month	monthly_revenue
242345	2/26/2016	700
242345	3/26/2016	700
242345	4/26/2016	700
242345	5/26/2016	700
242345	6/26/2016	700
242345	7/26/2016	700

Redshift Query to get above data set :-



WITH SampleData AS (
    SELECT
       CAST(242345 AS INTEGER) AS Item_Id
       ,CAST('2016-02-26' AS DATE) AS Start_Date
       ,CAST('2016-07-26' AS DATE) AS End_Date
       ,CAST(700 AS INTEGER) AS Monthly_Revenue
)

,cteTally AS (
        SELECT 0 AS TallyNum
        UNION ALL
        SELECT 1
        UNION ALL
        SELECT 2
        UNION ALL
        SELECT 3
        UNION ALL
        SELECT 4
        UNION ALL
        SELECT 5
        UNION ALL
        SELECT 6
        UNION ALL
        SELECT 7
        UNION ALL
        SELECT 8
        UNION ALL
        SELECT 9
        UNION ALL
        SELECT 10
        UNION ALL
        SELECT 11
    )
    SELECT
        Item_ID,
        DATEADD(MONTH,   c.TallyNum,   t.Start_date) AS "Month",
        Monthly_Revenue
    FROM  SampleData t  INNER JOIN cteTally c
    ON DATEDIFF(MONTH,   t.Start_Date,   t.End_Date) >= c.TallyNum ;