CODEWITHSUNDEEP
sqldatabasepostgresqldatewindow-functions
Aww
Aww

Reputation: 63

Duplicate records (rows) with modified values (Postgresql)

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:

enter image description here

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

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.

Upvotes: 2

Views: 58

Answers (1)

GMB
GMB

Reputation: 222462

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)

Upvotes: 1

Related Questions