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=1 234
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:

[code=oracle]
SQL> ed
Wrote file afiedt.buf

1* select ln, sysdate + ln - 1 dt from (select level ln from dual connect by level <= (to_date(sysdat e) - 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

[/code]

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.