Date manipulation across records

[LarrySteele]

Sorry if the subject is a bit vague, but I had a hard time creating one that accurately reflects what I'm trying to accomplish.

The scenario is this: I'll be receiving a file every day with updates to contracts. The data we receive needs a LOT of processing. Since it's not under our control, there's not a lot we can do with the quality of data in. If we press them, they will make changes, but the cost in time/money is beyond what the business is willing to pay. No worries, I've got most of it figured out, but there's one piece where I hit a slow down.

These contracts have start and dates. When either of these two dates change between our latest record and the new record, I need to take action. What I need to do is compare the new start date with the old end date. If new start date is just one day after the old end date, then they extended the contract and we'll actually use the old start date as the new start date.

[tt]
OLD RECORD.......................................
ORDER_NBR START_DATE END_DATE
5 01/01/2009 06/30/2009

NEW RECORD.(NEW START = OLD END +1 {EXTENSION})..
ORDER_NBR START_DATE END_DATE
5 07/01/2009 12/31/2009

INSERT RECORD....................................
ORDER_NBR START_DATE END_DATE
5 01/01/2009 12/31/2009
[/tt]

So, what I need to do is walk through our working table, and compare its start date to the end date for the current record in our data table. If new start = old end + 1, then extend. If new start > old end + 1, then we route the record to an error table. Otherwise, we take the dates given.

I'm pretty sure that I'll be using one or two cursors, but for some reason, just can't quite wrap my brain around what's required to set up the comparison, especially since I need to make my comparisons grouped by order numbers.

Any suggestions would be appreciated.

It helps to see real names, so here they are:

[tt]
Working table:
CNT_FIELDLASS
-ORDER_NBR
-START_DATE
-END_DATE

Data table:
CNT_DATA
-ORDER_NBR
-START_DATE
-END_DATE
[/tt]

Production system is on 11g, development is on 9i.

Thanks in advance,
Larry

 

[ChrisHunt]

Something like this?

DECLARE
   CURSOR c_con IS
      SELECT d.order_nbr,
             d.start_date old_start,
             d.end_date old_end,
             f.start_date new_start,
             f.end_date new_end,
             d.rowid d_row_id
             f.rowid f_row_id
      FROM   cnt_fieldlass f,
             cnt_data d
      WHERE  d.order_nbr = f.order_nbr;
BEGIN
   FOR r_con IN c_con LOOP
      IF r_con.new_start = r_con.old_end + 1 THEN
         UPDATE cnt_data
         SET end_date = r_con.new_end
         WHERE rowid = r_con.d_row_id;
      ELSIF r_con.new_start > r_con.old_end + 1 THEN
         -- Insert a row into the error table
      ELSE
         -- Do whatever it is you mean by "take the dates given"
      END IF;

      DELETE cnt_fieldlass
      WHERE rowid = r_con.f_row_id;
      
      -- You could do a commit here
   END LOOP
   -- ... or maybe here
END;

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[LarrySteele]

Thanks Chris. Funny how just posting the question finally kicked in the thought process. Here's what I've come up with so far...

select f.order_nbr,
       case when f.start_date = s.end_date +1
            then s.start_date
            else f.start_date
            end as start_date,
       f.end_date
from cnt_fieldglass f,
     (select order_nbr,
             start_date,
             end_date
      from cnt_data
      where {criteria}) s
where f.order_nbr=s.order_nbr
  and f.start_date < (s.end_date +2)

I haven't tested, but it looks like it ought to do the trick. Don't know why I couldn't figure that out, because it's actually pretty straight forward. Now I have the source for updating my working table.

Thanks again for the assist!

Larry