René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Range of dates with PL/SQL

create or replace type date_table as table of date
/


create or replace function date_range(from_dt in date, to_dt in date) 
  return date_table as
    a_date_table date_table := date_table();
    cur_dt date:=from_dt;
  begin
    while cur_dt <= to_dt loop
      a_date_table.extend;
      a_date_table(a_date_table.count) := cur_dt;
      cur_dt := cur_dt + 1;
    end loop;
  return a_date_table;
end date_range;
/

create table t_history (dt date, i number);

insert into t_history values(to_date('10.03.2003','dd.mm.yyyy'),5);
insert into t_history values(to_date('13.03.2003','dd.mm.yyyy'),3);
insert into t_history values(to_date('15.03.2003','dd.mm.yyyy'),1);


select dt_range.column_value, case when i is null then 0 else i end from table (
  cast ( date_range(
      to_date('10.03.2003','dd.mm.yyyy'),
      to_date('15.03.2003','dd.mm.yyyy')
    ) as date_table)
  ) dt_range left join t_history t on dt_range.column_value = t.dt
    order by
  dt_range.column_value;

drop table t_history;
drop type date_table;