CODEWITHSUNDEEP
sqlpostgresql
D. Watson
D. Watson

Reputation: 253

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

I have the next tables:

enter image description here enter image description here

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

enter image description here

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.

enter image description here

Upvotes: 3

Views: 89

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 2

Related Questions