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

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 11 Sep 2001 13:32:09 +0200
Message-ID: <9nksnq$u9o$1@s1.read.news.oleane.net>

"Richard Shea" <rshea_at_my-deja.com> a écrit dans le 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.

v815> define datefrom="2001-09-01"
v815> define dateto="2001-09-05"
v815> select to_char(to_date('&datefrom','YYYY-MM-DD')+rownum-1,'YYYY-MM-DD')

  2 from all_objects
  3 where rownum<=(to_date('&dateto','YYYY-MM-DD')-to_date('&datefrom','YYYY-MM-DD')+1)   4 /

TO_CHAR(TO

---

2001-09-01
2001-09-02
2001-09-03
2001-09-04
2001-09-05

5 rows selected.

--
Have a nice day
Michel

Received on Tue Sep 11 2001 - 06:32:09 CDT