Return row for each date in range 1

[hazelsisson]

Hi,

I have a simple query that retrieves appointments from a table. Some appointments are over multiple days; for these the field app_date_end is not null.

select a.app_date, a.app_date_end, a.app_id
from   m_calendar.appointments a
where  a.APP_DATE_END is not null;

Output:

APP_DATE    APP_DATE_END  APP_ID
--------------------------------
20/03/2006  21/03/2006    20086

I would like the query to return a row for each date in the range between (and including) the fields app_date and app_date_end.

Preferred output:

APP_DATE    APP_DATE_END  APP_ID
--------------------------------
20/03/2006  21/03/2006    20086
21/03/2006  21/03/2006    20086

I realise it would be easy if I was using a cursor but the query is for use in a report so that's not an option.

It sounds like it should be easy but I'm having a mental block! I think I'll need to use a pseudo-column somewhere but can't quite put my finger on it.

Thanks very much for any suggestions,
Hazel

 

[stefanhei]

Hi,
There may be more elegant solutions, but you may try this:

select * from appointments
/

APP_DATE  APP_DATE_     APP_ID
--------- --------- ----------
19-MAR-06 20-MAR-06          1
12-MAR-06 17-MAR-06          2

select b.app_date,a.app_date_end,a.app_id
  from appointments a,
      (select app_date+rn app_date, app_id
         from appointments, (select rownum-1 rn from all_objects) c
        where app_date+rn between app_date and app_date_end) b
 where a.app_id = b.app_id
/

APP_DATE  APP_DATE_     APP_ID
--------- --------- ----------
19-MAR-06 20-MAR-06          1
20-MAR-06 20-MAR-06          1
12-MAR-06 17-MAR-06          2
13-MAR-06 17-MAR-06          2
14-MAR-06 17-MAR-06          2

Stefan

 

[hazelsisson]

Perfect, that gives me exactly what I wanted.

It seems to be quite slow to run, but that's OK as it's just for small amounts of data.

Thanks very much Stefan.

Hazel

 

[stefanhei]

It's slow because all_objects returns a lot of rows. all_objects is used as a numbergenerator. You may use any other table instead, as long as it contains enough rows (# of rows > max. # of days an appointment lasts).

Stefan

 

[hazelsisson]

I see - I'll experiment. I think I'll be able to use another table rather than all_objects.

Thanks again,
Hazel