slipt the overlapped using sql

I have the following data:

And the date in usually in this order, that is, records will be adding to this table which changes the amount for previous months.

BeginDate            EndDate          Amount    
1/2003                1/2006                500
2/2003                8/2003                200  
1/2004                5/2004                100
4/2004               11/2005                 50  

I'd like to see results as follows:
BeginDate            EndDate            
1/2003              1/2003            500        
2/2003              8/2003            200
9/2003             12/2003           500
1/2004              3/2004            100
4/2004              11/2005           50
12/2005             1/2006           500

we have to split the dates in such way the when an amount is over written for previous months than the last enddate the, should be a new record.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Assuming there all values for BeginDate and EndDate are unique and you use standard date datatype, here's my stab at it (note: untested, but it should point you to the right direction):

  cursor get_overlapping_transactions is 
     select a.BeginDate LongBeginDate, a.EndDate LongEndDate, a.Amount, b.BeginDate ShortBeginDate, b.EndDate ShortEndDate
     from table_name a, table_name b
     where a.BeginDate < b.BeginDate
     and a.EndDate > b.EndDate
     order by 1,2;
  v_begin_date           table_name.BeginDate%type;
  v_end_date              table_name.EndDate%type;
  v_new_begin_date   table_name.BeginDate%type;
  v_amount table_name.Amount%type;
  v_begin_date := sysdate;  -- this should be actually set to an arbitrary date that is before any of the begin date values.  to be used for comparison
  v_end_date := sysdate;  -- same logic as above
  v_new_begin_date := null;
  for cur in get_overlapping_transactions loop
     if (v_begin_date is null or (v_begin_date is not null and v_begin_date != cur.LongBeginDate)) 
        and (v_end_date is null or (v_end_date is not null and v_end_date != cur.LongEndDate)) then

       if v_new_begin_date is not null then  -- you haven't finished the last part of the previous transaction
         insert into table_name (BeginDate, EndDate, Amount) values (v_new_begin_date, v_end_date, v_amount);
       end if;

       --This is a new overlapping transaction
       v_begin_date := cur.LongBeginDate;
       v_end_date := cur.LongEndDate;
       v_amount := cur.Amount;

       update table_name set EndDate := add_months(cur.ShortBeginDate, -1) 
        where BeginDate=cur.LongBeginDate 
        and EndDate = cur.LongEndDate;
       insert into table_name (BeginDate, EndDate, Amount) values (v_new_begin_date, add_months(cur.ShortBeginDate, -1) , v_amount);
     end if;
     v_new_begin_date := add_months(cur.ShortBeginDate, +1);
  end loop;

Open in new window

Also note that I did not put a commit in the procedure on purpose, so you can easily roll back in case something is wrong.

If it doesn't work and you need further help, a table dump would be most helpful (that's why I haven't tested - I'm lazy at creating test data).
mkarthik415Author Commented:
BeginDate and EndDate are may not be unique, in such a case data senario can be

BeginDate            EndDate          Amount    
1/2003                1/2006                500
2/2003                8/2003                200  
8/2003                5/2004                100
4/2004               11/2005                 50  

I'd like to see results as follows:
BeginDate            EndDate            
1/2003              1/2003            500        
2/2003              7/2003            200
8/2003              5/2004            100
4/2004              11/2005           50
12/2005             1/2006           500

trying to get the results in a search statement instead of making a insert statement into the table or update the table.
Regarding not updating the table, you can modify the procedure I made above to populate a collection, then select from that collection casted as a table.

I'm suddenly busy at the moment but if nobody else helps you and I get some idle time later, I'll take another stab at it.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Okay, while my process is running, would it be possible for more than one row to overlap the same set of  other rows?  Something like:

BeginDate            EndDate          Amount    
1/2003                1/2006                500
2/2003                5/2003                200
5/2003                6/2004               1000  
8/2003                5/2004                100
4/2004               11/2005                 50

If so I can't see how a single select statement can do it.  If such is not possible, I have an idea for a single but complex query which I can try.
Also, what's your primary key column for the table?
mkarthik415Author Commented:
primary key in this table is date column which I did not mention in the above table but it is in the ascending order, that is month and year at the point when the new record is inserted into this table .
mkarthik415Author Commented:
yes it be possible for more than one row to overlap the same set of  other rows.
like your senario.

BeginDate            EndDate          Amount     AddedDate
1/2003                1/2006                500            1/2010
2/2003                5/2003                200            2/2010
5/2003                6/2004               1000           3/2010
8/2003                5/2004                100            4/2010
4/2004               11/2005                 50            5/2010
Hi.  I've been thinking about this and realized it's quite a tough cookie.  Given the possible overlaps I think you will need a recursive procedure to check if the rows overlapped by one row also overlap others, and so on.

Right now I can't justify spending time on it unless I either get paid for it or at least know if I can use it elsewhere - can I know the background and practical application of this particular problem?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.