How to populate a table with data intervals on postgresql with a function

Question

I have the next tables:

I need to create/populate the third table as following:

I painted the cells to make easier to understand. If I need to spell what I need, would be as follows:

Between 2018-06-05 and 2018-06-19 the value is 50.

Between 2018-06-19 and 2018-06-21 the value is 150.

Between 2018-06-21 and 2018-06-25 the value is 180.

Between 2018-06-25 and 2018-07-05 the value is 200.

I need to create a function to do this but I can't do this, I spend all day thinking about this problem but unhappily I can't dev the script.

I tried use some WHILE but....not success.

I don't understand the logic used to generate the two date columns in your expected output. Can you explain what is happening there? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 6, 2018 at 2:00

Gordon Linoff · Accepted Answer · 2018-07-06 02:07:17Z

2

Hmmm. I'm thinking you can union all the tables together and then use window functions to fill in the details:

select date as startdate,
       lead(date) over (order by date) as enddate,
       coalesce(newv,
                lead(prev) over (order by date)
               ) as value
from ((select date, null as prev, null as newv
       from table1
      ) union all
      (select dateupdate, prev, newv
       from table2
      )
     ) tt;

This adds an extra row for the last date. If you don't want that, you can remove it by using an additional subquery and filtering it out.

answered Jul 6, 2018 at 2:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

D. Watson Over a year ago

Excelent my friend. Worked very well. Thanks for all! You're the MAN! Have a nice day.

Collectives™ on Stack Overflow

How to populate a table with data intervals on postgresql with a function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related