Duplicate records (rows) with modified values (Postgresql)

Question

I have a dataset that I am preparing for a pivot view (in Excel). It's customer data and I want to create a view that allows to summarize how many customers were active every month. Therefore I need to duplicate rows with a modified value for first_status_date. This is my data:

This is where I am trying to get: (The green rows are duplicated, and the cells in bold are modified.

Because I am adding a month on top of the value of the cell above, I am thinking of working with the lag function. But I am not familiar with duplicating rows. The id 123 and 356 needed 2 duplicated rows because there is a 2 month difference between last and first status date, the id 221 needs only 1 duplicated row, as there is a one month difference.

GMB · Accepted Answer · 2020-07-26 21:46:05Z

1

You can use generate_series() to generate the new rows - a little tweaking is needed for the end date to ensure that we do get a row for the last month, regardless of the actual month day:

select
    t.id,
    t.first_status,
    d.first_status_date,
    t.last_status,
    t.last_status_date
from mytable t
cross join lateral generate_series(
    t.first_status_date, 
    date_trunc('month', t.last_status_date) + interval '1 month',
    interval '1 month'
) d(first_status_date)

edited Jul 26, 2020 at 21:46

answered Jul 26, 2020 at 20:35

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Aww Over a year ago

I get a syntax error: [42601] ERROR: syntax error at or near "interval"

GMB Over a year ago

@Aww: there is a missing comma after the second argument to generate_sries()... Fixed.

Aww Over a year ago

It still gives me this: [42601] ERROR: syntax error at end of input. What exactly is d(first_status_date) doing? That is where it shows me the error, like something is missing.

Aww Over a year ago

I added on true right after d(first_status_date) and it seems to work now. Thank you!

GMB Over a year ago

@Aww: my bad. Actually a cross join lateral is closer to what you need. I changed the query accordingly.

|

Collectives™ on Stack Overflow

Duplicate records (rows) with modified values (Postgresql)

1 Answer 1

6 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Related