Thread: date interpolation

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

> 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.

Looks to me like the perfect candidate for a Set Returning Function.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc