Augmenting a table sorted grouped by month to one grouped by day

[ericjb]

I have a table with many columns in it. Each row has an ORDER_ID, ORDER_LINE_ID, START_DATE, and END_DATE. The combination of said columns make the row unique. Currently, the row has a start date and end date that is in month intervals. What I need to do is expand the table so that all of the data is still the same, only instead of month intervals it would be days.

For example, one row has
ORDER_ID=1000
ORDER_LINE_ID=1234
START_DATE=11/1/07
END_DATE=12/1/07 (Ranges can be more than one month as well)

I have a DATE column and want to augment the example row into 31 rows (one for each day in the interval; this day would go in the DATE column)

I need some kind of script that would be able to do this for thousands of rows.

How would I go about doing this?

Thanks in advance!

[amitpatel66]

Check this:

SQL> ed
Wrote file afiedt.buf
 
  1* select ln, sysdate + ln - 1 dt from (select level ln from dual connect by level <= (to_date(sysdate) - to_date(:mydate)))
SQL> /
 
        LN DT
---------- ----------
         1 08/01/2008
         2 09/01/2008
         3 10/01/2008
         4 11/01/2008
         5 12/01/2008
         6 13/01/2008
         7 14/01/2008
         8 15/01/2008
         9 16/01/2008
        10 17/01/2008
        11 18/01/2008
 
        LN DT
---------- ----------
        12 19/01/2008
        13 20/01/2008
        14 21/01/2008
        15 22/01/2008
        16 23/01/2008
        17 24/01/2008
        18 25/01/2008
        19 26/01/2008
        20 27/01/2008
        21 28/01/2008
        22 29/01/2008
 
        LN DT
---------- ----------
        23 30/01/2008
        24 31/01/2008
        25 01/02/2008
        26 02/02/2008
        27 03/02/2008
        28 04/02/2008
        29 05/02/2008
        30 06/02/2008
        31 07/02/2008
        32 08/02/2008
        33 09/02/2008
 
        LN DT
---------- ----------
        34 10/02/2008
        35 11/02/2008
        36 12/02/2008
        37 13/02/2008
        38 14/02/2008
 
38 rows selected.
 
SQL> print :mydat
 
MYDAT
--------------------------------------------------------------------------------
01/12/2007
 
 

I have taken the difference of two dates and displayed the dates (sysdate + ln) between that range. Use this logic and implement your requirement. Let me know in caseof any issues.