I have a sample table:

I would like to get the following output

I have tried using GENERATE_SERIES() in Amazon Redshift, but that does not give the required result.

The existing table is quite large so I could use temp tables then join back to another table at a later stage.

I have trawled through other posts, but other proposed solutions isn't quite giving the desired results / don't work at all on Amazon Redshift. Any help in solving this would be appreciated.

CodePudding user response：

The traditional method would be:

• Create a Calendar table that contains one row per month, with start_date and end_date columns
• Join your table to the Calendar table, where table.start_dt <= calendar.end_dt AND table.end_dt >= calendar.start_dt
• The two columns would be:
  • GREATEST(table.start_dt, calendar.start_dt)
  • LEAST(table.end_dt, calendar.end_dt)