Generating multiple rows from a single row based on dates

Question

I have a database table with a start date and a number of months. How can I transform that into multiple rows based on the number of months?

I want to transform this

Into this:

Answer 1

You may not need so many subqueries but this should help you understand how it can be broken down

WITH date_minmax AS(
    SELECT
        min(start_date) as date_first,
        (max(start_date) + (month_count::text || ' months')::interval)::date AS date_last
    FROM "your_table"
    GROUP BY month_count
), series AS (
    SELECT generate_series(
        date_first,
        date_last,
        '1 month'::interval
    )::date as list_date
    FROM date_minmax
)
SELECT
    id as subscription_id,
    list_date as date,
    amount_monthly as amount
FROM series
JOIN "your_table"
    ON list_date <@ daterange(
            start_date,
            (start_date + (month_count::text || ' months')::interval)::date
    )
ORDER BY list_date

This should achieve the desired result http://www.sqlfiddle.com/#!17/7d943/1

Answer 2

OK, it's very easy to implement this in PostgreSQL, just use generate_series, as below:

select * from month_table ;
  id  | start_date | month_count | amount | amount_monthly 
------+------------+-------------+--------+----------------
 1382 | 2017-09-01 |           3 |     38 |           1267
 1383 | 2018-02-01 |           6 |     50 |            833
(2 rows)

select                     
    id,
    generate_series(start_date,start_date + (month_count || ' month') :: interval - '1 month'::interval, '1 month'::interval)::date as date,
    amount_monthly
from
    month_table ;
  id  |    date    | amount_monthly 
------+------------+----------------
 1382 | 2017-09-01 |           1267
 1382 | 2017-10-01 |           1267
 1382 | 2017-11-01 |           1267
 1383 | 2018-02-01 |            833
 1383 | 2018-03-01 |            833
 1383 | 2018-04-01 |            833
 1383 | 2018-05-01 |            833
 1383 | 2018-06-01 |            833
 1383 | 2018-07-01 |            833
(9 rows)

Answer 3

This can easily be done using generate_series() in Postgres

select t.id, 
       g.dt::date,
       t.amount_monthly
from the_table t
   cross join generate_series(t.start_date, 
                              t.start_date + interval '1' month * (t.month_count - 1),  
                              interval '1' month) as g(dt);

Answer 4

We can try using a calendar table here, which includes all possible start of month dates which might appear in the expected output:

with calendar as (
    select '2017-09-01'::date as dt union all
    select '2017-10-01'::date union all
    select '2017-11-01'::date union all
    select '2017-12-01'::date union all
    select '2018-01-01'::date union all
    select '2018-02-01'::date union all
    select '2018-03-01'::date union all
    select '2018-04-01'::date union all
    select '2018-05-01'::date union all
    select '2018-06-01'::date union all
    select '2018-07-01'::date union all
    select '2018-08-01'::date
)

select
    t.id as subscription_id,
    c.dt,
    t.amount_monthly
from calendar c
inner join your_table t
    on c.dt >= t.start_date and
       c.dt < t.start_date + (t.month_count::text || ' month')::interval
order by
    t.id,
    c.dt;

Demo