using a line in a database on the prevous line to create a date range

[JamesFlowers]

Hi,

I have a dataset that has a part number and a date

part date
1 01/01/2005
1 01/02/2005
1 01/03/2005

this date is the "phase in" date, and I want to constrct a "phase out" date using the next line date-1 to create the range and adding sysdate as the last date.

part date out_date
1 01/01/2005 31/01/2005
1 01/02/2005 28/05/2005
1 01/03/2005 sysdate


Any ideas

TIA

Regards

JmesFlowers-Smiths Detection , Watford , UK


James Flowers
Crystal Consultant

 

[Dagon]

update table t1 set phase_out_date =
(select nvl(min(t2.phase_in_date) - 1, sysdate)
from table t2
where t2.part_no = t1.part_no
and t2.phase_in_date > t1.phase_in_date)

 

[stefanhei]

I think analytic functions would be more efficient here:

select * from t;
/

      PART PHASE_IN
---------- ---------
         1 01-JAN-05
         1 01-FEB-05
         1 01-MAR-05
         1 15-APR-05

select part,
       phase_in,
       (lag(phase_in,1,sysdate) over (order by phase_in desc))-1 phase_out
  from t
 order by phase_in asc
/

      PART PHASE_IN  PHASE_OUT
---------- --------- ---------
         1 01-JAN-05 31-JAN-05
         1 01-FEB-05 28-FEB-05
         1 01-MAR-05 14-APR-05
         1 15-APR-05 02-MAY-05

Stefan

 

[Dagon]

Wouldn't you need a partition clause for the "part" ?

(lag(phase_in,1,sysdate) over (partition by part order by phase_in desc))-1 phase_out

Otherwise, it will presumably compute the previous date value over the whole set of dates rather than just within the part grouping.