How Do I?

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 Received on Sat Sep 04 2004 - 07:37:38 CDT