Re: SQL to have a row for every single day

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 21 Mar 2003 23:39:08 GMT
Message-ID: <b5g7qs$28d417$2@ID-82536.news.dfncis.de>

> I would like to have a resultset that has every day in a certain range
> appear at least once, in conjuction with my table called t_history which
> will have records for some days.
>
> For example:
> Table t_history has fields mydate(date) and count(integer).
>
> t_history has these records:
>
> March 10, 2003 5
> March 13, 2003 3
> March 15, 2003 1
>
>
> I'd like to use a SQL with a range of March 10-15 and have the following
> resultset returned:
> March 10, 2003 5
> March 11, 2003 0
> March 12, 2003 0
> March 13, 2003 3
> March 14, 2003 0
> March 15, 2003 1
>
> The zero's can be nulls as well, I don't care.
>
> In a way, what I would like is an outer join to a table that had a record
> for every single day. Any ideas how I can go about this?

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;

hth

Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html

Received on Fri Mar 21 2003 - 17:39:08 CST