Re: How Do I?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 7 Sep 2004 09:21:57 GMT
Message-ID: <slrncjqvdl.3fs.rene.nyffenegger@zhnt60m34.netarchitects.com>

In article <413C625D.EF84F7D4_at_NoSpambeliveau.ccHere>, Harvey wrote:

> Rene Nyffenegger wrote:

>>
>> > Hi Rene:
>> >
>> > Thanks - it's close. The last part of it displays everything the way it
>> > needs to be, however, it isn't actually updating the end_dt in the
>> > table.
>> >
>> > Here's what I'm getting:
>> >
>> > EMPL_ID EFFECT_DT
>> > NVL(NVL(END_DT,LEAD(EFFECT_DT)OVER(ORDERBYEFFECT_DT)-1),'31-DEC-2078')
>> >======= ====================
>> >======================================================================
>> > BXXXXX 22-JUN-1998 00:00:00 30-JUN-1999 00:00:00
>> > BXXXXX 01-JUL-1999 00:00:00 31-JUL-1999 00:00:00
>> > BXXXXX 01-AUG-1999 00:00:00 31-JAN-2000 00:00:00
>> > BXXXXX 01-FEB-2000 00:00:00 31-DEC-2001 00:00:00
>> > BXXXXX 01-FEB-2000 00:00:00 31-DEC-2001 00:00:00
>> > BXXXXX 01-JAN-2002 00:00:00 15-MAR-2002 00:00:00
>> > BXXXXX 04-MAR-2002 00:00:00 15-MAR-2002 00:00:00
>> > BXXXXX 16-MAR-2002 00:00:00 15-DEC-2002 00:00:00
>> > BXXXXX 16-DEC-2002 00:00:00 17-AUG-2003 00:00:00
>> > BXXXXX 18-AUG-2003 00:00:00 15-AUG-2004 00:00:00
>> > BXXXXX 16-AUG-2004 00:00:00 31-DEC-2078 00:00:00
>> >
>> > 11 ROWS SELECTED
>> >
>> > This does show that the logic is correct. The contents of the table
>> > remain:
>> >
>> > EMPL_ID EFFECT_DT END_DT
>> >======= ==================== ====================
>> > BXXXXX 22-JUN-1998 00:00:00
>> > BXXXXX 01-JUL-1999 00:00:00
>> > BXXXXX 01-AUG-1999 00:00:00
>> > BXXXXX 01-FEB-2000 00:00:00 31-DEC-2001 00:00:00
>> > BXXXXX 01-FEB-2000 00:00:00 31-DEC-2001 00:00:00
>> > BXXXXX 01-JAN-2002 00:00:00 15-MAR-2002 00:00:00
>> > BXXXXX 04-MAR-2002 00:00:00
>> > BXXXXX 16-MAR-2002 00:00:00 15-DEC-2002 00:00:00
>> > BXXXXX 16-DEC-2002 00:00:00 17-AUG-2003 00:00:00
>> > BXXXXX 18-AUG-2003 00:00:00 15-AUG-2004 00:00:00
>> > BXXXXX 16-AUG-2004 00:00:00 31-DEC-2078 00:00:00
>> >
>> > BTW, these are only three of the columns in the table - there are about
>> > 50 in it actually.
>> >
>> > Your assistance is MUCH appreciated!
>> >
>> > Harvey
>>
>> Do you have a primary key on the table to be updated?
>>
>> --
>> Rene Nyffenegger
>> http://www.adp-gmbh.ch/

> 
> Not on the work table - I did create the work table sorted though, so
> the records are in the correct sequence.
> 
> Harvey

Harvey, as there is no primary key and (as far as I can tell from your sample data) no way to uniquely identify a record, I cannot think of a 100% sure way to do what you want. However, the following approach should get you into the right direction:

alter session set nls_date_format='dd.MON.yyyy';

create table fn(
  empl_id char(6),
  effect_dt date,
  end_dt date
);

insert into fn values
('BXXXXX', '22-JUN-1998', null);
insert into fn values
('BXXXXX', '01-JUL-1999', null);
insert into fn values
('BXXXXX', '01-AUG-1999', null);
insert into fn values
('BXXXXX', '01-FEB-2000', '31-DEC-2001'); insert into fn values
('BXXXXX', '01-FEB-2000', '31-dec-2001'); insert into fn values
('BXXXXX', '01-JAN-2002', '15-MAR-2002'); insert into fn values
('BXXXXX', '04-MAR-2002', null);
insert into fn values
('BXXXXX', '16-MAR-2002', '15-DEC-2002'); insert into fn values
('BXXXXX', '16-DEC-2002', '17-AUG-2003'); insert into fn values
('BXXXXX', '18-AUG-2003', '15-AUG-2004'); insert into fn values
('BXXXXX', '16-AUG-2004', '31-DEC-2078'); create table fn_ as
select
  empl_id,
  effect_dt,
  nvl(
    nvl(end_dt, lead(effect_dt) over(order by effect_dt) -1),     '31-DEC-2078'
    ) new_end_dt
from fn;

update fn set end_dt = (
  select new_end_dt from fn_
  where fn_.empl_id = fn.empl_id and

        fn_.effect_dt = fn.effect_dt)
where end_dt is null;

select * from fn_;

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/

Received on Tue Sep 07 2004 - 04:21:57 CDT