SQL: get every day between two days 1

[stwi1974]

Hi,

i got begindate (01/01/2004) and enddate (01/01/2004) and i need a sql-statement which results
01/01/2004, field, ...
01/02/2004, field, ...
01/03/2004, field, ...
.
.
.

without using a statement like
select d.date, a.field, ...
from table_1 a
join table_2 b on b.date between a.begin and a.end

Is there any way to get all days between with a sql-statement avoiding user-defined functions?

Thanks alot in advance

 

[ddrillich]

Please have a look at:

thread186-646741

 

[SantaMufasa]

Zerberus,

Although I wrote the solution to which Dan (ddrillich) refers, I don't believe it will resolve your need. I'm certain we can create a non-user-defined-function solution for you, but I need to know a bit more information. Is it correct that in your table_1 you have at least the following:

SQL> desc table_a
 Name                    Null?    Type
 ----------------------- -------- -------------
 BEGIN_DT                NOT NULL DATE
 END_DT                  NOT NULL DATE
 FIELD                            VARCHAR2(nn)
 ...

And for a given row in table_a, you want the same field value displayed for each date between BEGIN_DT and END_DT? If so, what is the maximum number of days between the two dates? Could the two dates be separated by years?

Following is a solution for you. I chose the data dictionary view "ALL_TAB_COLS" as a dummy driver table to generate a large number of numeric values to generate dates between your BEGIN_DT and END_DT. For best performance, you should replace ALL_TAB_COLS with a permanent table/view that has the fewest rows that will always exceed the maximum number of days between your BEGIN_DT and END_DT. (For example, if your maximum number of days between BEGIN_DT and END_DT is 45, then choose a table/view that has around 50 rows.) Here are 1) a sample table_a, 2) the SQL SELECT code, and 3) the results:

1) Sample Data:
SQL> select * from table_a;

BEGIN_DT  END_DT    FIELD
--------- --------- ------------------
29-JAN-04 11-FEB-04 yada, yada, yada
08-FEB-04 13-FEB-04 some other message

2) Your Query:
select begin_dt+rn2 dt,field
from 	 (select rownum rn1, table_a.* from table_a)
	,(select rownum rn2 from all_tab_cols)
where begin_dt+rn2 <= end_dt
order by rn1, dt
/

3) Your Results:
DT        FIELD
--------- ------------------
30-JAN-04 yada, yada, yada
31-JAN-04 yada, yada, yada
01-FEB-04 yada, yada, yada
02-FEB-04 yada, yada, yada
03-FEB-04 yada, yada, yada
04-FEB-04 yada, yada, yada
05-FEB-04 yada, yada, yada
06-FEB-04 yada, yada, yada
07-FEB-04 yada, yada, yada
08-FEB-04 yada, yada, yada
09-FEB-04 yada, yada, yada
10-FEB-04 yada, yada, yada
11-FEB-04 yada, yada, yada
09-FEB-04 some other message
10-FEB-04 some other message
11-FEB-04 some other message
12-FEB-04 some other message
13-FEB-04 some other message

18 rows selected.

Let me know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:17 (11Feb04) UTC (aka "GMT" and "Zulu&quot, 11:17 (11Feb04) Mountain Time)

 

[SantaMufasa]

Don't you just hate it when you notice a slight problem AFTER you hit the [Submit Post] key? To include the BEGIN_DT in your output, use this slightly adjusted code:

select begin_dt+(rn2-1) dt,field
from 	 (select rownum rn1, table_a.* from table_a)
	,(select rownum rn2 from all_tab_cols)
where begin_dt+(rn2-1) <= end_dt
order by rn1, dt
/

Sorry,

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:22 (11Feb04) UTC (aka "GMT" and "Zulu&quot, 11:22 (11Feb04) Mountain Time)

 

[stwi1974]

Thanks alot for your help, Mufasa and ddrillich!

now the statement looks like

select primary_key, begin_dt+(rn-1) dt
from table, (select rownum rn from smaller_table)
where begin_dt+(rn-1) <= end_dt and
primary_key=value
order by dt

Zerberus