Suppose I have a table like this:
| subject | flag | first_date | last_date |
|---|---|---|---|
| this is a test | 2 | 1/1/2016 | 1/4/2016 |
into something like this:
| subject | flag | date |
|---|---|---|
| this is a test | .5 | 1/1/2016 |
| this is a test | .5 | 1/2/2016 |
| this is a test | .5 | 1/3/2016 |
| this is a test | .5 | 1/4/2016 |
Is there an easy way to do this?
You can use generate_series() to produce list of consecutive days between first_date and last_date:
with dates as (
select d::date, last_date- first_date+ 1 ct
from test, generate_series(first_date, last_date, '1d'::interval) d
)
select subject, flag/ ct flag, d date
from dates
cross join test;
subject | flag | date
----------------+------------------------+------------
this is a test | 0.50000000000000000000 | 2016-01-01
this is a test | 0.50000000000000000000 | 2016-01-02
this is a test | 0.50000000000000000000 | 2016-01-03
this is a test | 0.50000000000000000000 | 2016-01-04
(4 rows)
