Re: filling in missing dates in a time series

carol_marra_at_msn.com wrote:

> To date, we have done this by outer joining with a table that holds all
> dates for the time step in question (hourly, daily, monthly, etc). It
> works, but it's not very elegant or practical.
>
> Other thoughts on how to achieve this? (Apologies if there are
> objections to this being posted in the theories group; it seems a
> reasonable place to me.)
>
> Thanks,
> carol

The most efficient way is to use a pipelined table function: A function that can be queried as though it was a table. Here is an example that will generate, on-the-fly, any series of dates given a starting and ending date.

CREATE OR REPLACE TYPE date_array AS TABLE OF DATE; /

CREATE OR REPLACE FUNCTION date_table(sdate DATE, edate DATE) RETURN date_array PIPELINED AS

BEGIN
   FOR i IN 0 .. (edate - sdate)
   LOOP
     PIPE ROW(sdate + i);
   END LOOP;
   RETURN;
END date_table;
/

A complete demo showing how to use it can be found in Morgan's Library at www.psoug.org under Pipelined Table Functions.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)