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.

Thanks!!!
mkarthik415Asked:
Who is Participating?
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.

johanntagleCommented:
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):

declare
  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;
begin
  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;
     else
       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;
end;

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).
0
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.
0
johanntagleCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johanntagleCommented:
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.
0
johanntagleCommented:
Also, what's your primary key column for the table?
0
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 .
0
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
0
johanntagleCommented:
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?
0

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.