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:

Most people here want sample table data and expected result as formatted text, not as images. — jarlh
– jarlh, Commented Jun 5, 2019 at 7:28
See this question on formatting. Or the help section that is available through the ? when you edit your question. — user330315
– user330315, Commented Jun 5, 2019 at 10:49
@a_horse_with_no_name well it doesn't really say anything about tables. — allanth
– allanth, Commented Jun 6, 2019 at 11:17

Tim Biegeleisen · Accepted Answer · 2019-06-05 07:35:35Z

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

user330315 · Accepted Answer · 2019-06-05 07:52:52Z

0

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);

answered Jun 5, 2019 at 7:52

user330315

Comments

Shawn.X · Accepted Answer · 2019-06-05 09:00:19Z

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)

Lucas · Accepted Answer · 2019-06-05 09:01:34Z

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

Collectives™ on Stack Overflow

Generating multiple rows from a single row based on dates

4 Answers 4

Demo

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related