I am looking for a way to interpolate dates to create a nice output table.
example:
given this table:
id | start_date | end_date
----+------------+-----------
101 | 2003-02-03 | 2003-02-05
102 | 2003-03-20 | 2003-03-24
:
:
I am looking for a SELECT to retrieve this table:
id | date
----+------------
101 | 2003-02-03
101 | 2003-02-04
101 | 2003-02-05
102 | 2003-03-20
102 | 2003-03-21
102 | 2003-03-22
102 | 2003-03-23
102 | 2003-03-24
:
:
So far the closest solution I have come up with involves creating a dummy
table that contains a long list of dates that I can JOIN against in order
to fill in the interpolated values. It doesn't work very well, and gets
slow with large tables.
any clever ideas would be appreciated.
Rob