Re: How Do I?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 4 Sep 2004 19:25:40 GMT
Message-ID: <slrncjk5lf.1og.rene.nyffenegger@zhnt60m34.netarchitects.com>

In article <4139B712.68C23997_at_NoSpambeliveau.ccHere>, Harvey wrote:
> Hi All:
>
> I'm working on a project where I'm building a table, pulling data from
> others. One of the problems that I'm working out is valuing missing
> fields for a column that is defined as not null. Here's an example:
>
> 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

>
> Where I need to get to is where the end_dt column is valued with the
> effect_dt - 1 of the next record, and if there is no next record (i.e,
> the 16-Aug-2004 record), then the value of end_dt is set to 31-Dec-2078.
>
> If it were only a few records I'd do it by hand, but in this case it's a
> couple thousand.
>
> Any assistance will be GREATLY appreciated!

Harvey,

  The following should bring you closer. Not sure if it works on 8i though.

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'); select
  empl_id,
  effect_dt,
  nvl(
    nvl(end_dt, lead(effect_dt) over(order by effect_dt) -1),     '31-DEC-2078'
    )
from fn;

hth,
Rene

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

Received on Sat Sep 04 2004 - 14:25:40 CDT