Using the model clause to expand dates

Question

I have several different types of data involving date range that I want to merge together, but at the same time broken down by day. So a 3 day piece of data would result in three rows:

start    primary_key
start+1  primary_key
start+2  primary_key

I've been playing around using the model clause of the select statement in 10g and was looking for the best way to achieve this. Currently I'm joining a range of dates that covers the full range of possible dates (select min(start date), max(end date)). I'd prefer to be selecting the data and adding in more rows to transform it to a per day dataset.

edit:

I've managed to come up with (now includes sample data):

    SELECT * FROM (
        SELECT 123 req_code, 
               345 req_par_code, 
               TO_DATE('01-03-2010', 'dd-mm-yyyy') req_start_date, 
               TO_DATE('05-03-2010', 'dd-mm-yyyy') req_end_date
        FROM dual
)

    MODEL 
        PARTITION BY (req_code)
        DIMENSION BY (0 d)
        MEASURES     (SYSDATE dt, req_par_code, req_start_date, req_end_date)
        RULES ITERATE(365) UNTIL (dt[iteration_number] >= TRUNC(req_end_date[0])) (
            dt[iteration_number] = NVL(dt[iteration_number-1] + 1, TRUNC(req_start_date[0])),

            --Copy data across
            req_par_code[ iteration_number ] = req_par_code[0],
            req_start_date[ iteration_number ] = req_start_date[0],
            req_end_date[ iteration_number ] = req_end_date[0]
        )
    ORDER BY dt, req_code;

Answer 1

SELECT  TO_DATE('01.01.2009', 'dd.mm.yyyy') + level - 1
FROM    dual
CONNECT BY
        TO_DATE('01.01.2009', 'dd.mm.yyyy') + level <= TRUNC(SYSDATE, 'DDD') + 1

will give you the list of all dates from Jan 1st, 2009 till today.

Answer 2

Hi Chris,

you can use the MODEL clause to generate rows, here's a small example:

SQL> SELECT * FROM t_data;

        PK START_DATE  END_DATE
---------- ----------- -----------
         1 20/01/2010  20/01/2010
         2 21/01/2010  23/01/2010
         3 24/01/2010  27/01/2010

SQL> SELECT pk, start_date, end_date FROM t_data
  2  MODEL
  3     PARTITION BY (pk)
  4     DIMENSION BY (0 AS i)
  5     MEASURES(start_date, end_date)
  6     RULES
  7     (  start_date[FOR i
  8                   FROM 1 TO end_date[0]-start_date[0]
  9                   INCREMENT 1] = start_date[0] + cv(i),
 10        end_date[ANY] = start_date[CV()] + 1
 11     )
 12  ORDER BY 1,2;

        PK START_DATE  END_DATE
---------- ----------- -----------
         1 20/01/2010  21/01/2010
         2 21/01/2010  22/01/2010
         2 22/01/2010  23/01/2010
         2 23/01/2010  24/01/2010
         3 24/01/2010  25/01/2010
         3 25/01/2010  26/01/2010
         3 26/01/2010  27/01/2010
         3 27/01/2010  28/01/2010

Answer 3

Despite loving the SQL model clause, in this case it's only second or third best. I've compared six ways to do this type of query here on my blog: http://rwijk.blogspot.com/2007/11/interval-based-row-generation.html

Hope this helps.

Regards, Rob.