Re: one row for each day in date range ? [opposite of 'join' ?]

From: Mark D Powell <mark.powell_at_eds.com>
Date: 11 Sep 2001 07:05:45 -0700
Message-ID: <178d2795.0109110605.5af015dd@posting.google.com>

rshea_at_my-deja.com (Richard Shea) wrote in message news:<43160f6f.0109110154.6cd76275_at_posting.google.com>...
> Hi - I've got a table with two columns :
>
> datefrom
> dateto
>
> I would like to produce a view which contained (for each record
> selected) a row for each day in the range. That is if I selected one
> row with values :
>
> datefrom=2001-09-01
> dateto=2001-09-05
>
> I got in the view something like this :
>
> deriveddate datefrom dateto
> 2001-09-01 2001-09-01 2001-09-05
> 2001-09-02 2001-09-01 2001-09-05
> 2001-09-03 2001-09-01 2001-09-05
> 2001-09-04 2001-09-01 2001-09-05
> 2001-09-05 2001-09-01 2001-09-05
>
> It seems to me this is sort of the opposite of 'group by' !
>
> Anyone got any ideas ?
>
> thanks
>
> richard shea.

Here is some SQL that should answer your request. I based if off of a Thomas Kyte post and someone's post a similar SQL.

rem
rem Generate a sequential list of dates between and including the rem end dates. Table used in sub-select must have enough rows to rem equal maximum requested range (difference in dates) rem
select to_char( to_date(&&start_date,'yyyymmdd') + rownum - 1,

       'DD-MON-YYYY') the_date
from ( select rownum

         from   all_objects
        where rownum <= ( to_date(&end_date,'YYYYMMDD') -
                          to_date(&&start_date,'YYYYMMDD') + 1
                        )
       )

;
undefine start_date
undefine end_date

• Mark D Powell --

Received on Tue Sep 11 2001 - 09:05:45 CDT