Re: Oracle Date SQL challenge

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 11 Aug 1998 21:32:01 GMT
Message-ID: <35d0b771.32728671@192.86.155.100>

A copy of this was sent to allenh_at_Starbase.NeoSoft.COM (Allen) (if that email address didn't require changing) On 11 Aug 1998 15:23:17 -0500, you wrote:

>
>Here is the problem:
>
> given a min and a max date, create a SQL statement to return a date
> for each day between min_date and max_date (ie, for use in the
> creation of a view).
>
> min(start_date) and max(start_date) are in a table, and I want to get
> a list of all days between them (but not all are in that table, since
> it does not include Sat, Sun, and Holidays).
>
> This gets tricky - If I have just 2 days: 1-jan and 10-jan, I still
> want to return 10 rows, 1 for each date.
>
> Any ideas? Yes, I could always write a PL/SQL procedure to do this,
> but I was wondering if there was a simple SQL statement that might
> also work. Thx..

You need to have a table with N rows in it (where the max number of rows/dates you ever need to generate is N). I might use ALL_OBJECTS which has from 500 - a couple of thousand objects and is always there.

Then, you can (assuming a table T with 1 row min_start_date and max_start_date):

select min_start_date+rownum-1
  from T, all_objects
 where rownum <= max_start_date-min_start_date /

for example:

SQL> create table testing ( min_start_date date, max_start_date date );

Table created.

SQL>
SQL> insert into testing values ( sysdate-10, sysdate+5 );

1 row created.

SQL>
SQL> select min_start_date+rownum-1
  2 from testing, all_objects
  3 where rownum <= max_start_date-min_start_date   4 /

MIN_START

---

01-AUG-98
02-AUG-98
03-AUG-98
04-AUG-98
05-AUG-98
06-AUG-98
07-AUG-98
08-AUG-98
09-AUG-98
10-AUG-98
11-AUG-98
12-AUG-98
13-AUG-98
14-AUG-98
15-AUG-98

15 rows selected.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Aug 11 1998 - 16:32:01 CDT