oracle-databasefunctionplsqlanalytical

Oracle PL/SQL: A SELECT statement replicating the START_DATE of the first record with END_DATE NOT NULL

This is the starting situation: enter image description here

If END_DATE is NOT NULL, then the previous empty records (= records with NO START_DATE and NO END_DATE) have to be filled with the START_DATE referred to that END_DATE. The final situation has to be the following:

enter image description here

How can I achieve this with a simple SELECT statement, using Oracle analytical functions?

Thank you very much for considering my request.

Solution

  • With sample data you posted (didn't feel like typing everything):

    SQL> with test (id_activity, code, start_date, end_date) as
  2    (select 4, 'A00', null             , null              from dual union all
  3     select 7, 'A01', null             , null              from dual union all
  4     select 6, 'A02', null             , null              from dual union all
  5     --
  6     select 3, 'A03', date '2001-01-10', date '2002-06-30' from dual union all
  7     select 4, 'A04', date '2001-10-04', date '2004-07-31' from dual
  8    )

    first_value analytic function might help:

      9  select id_activity,
 10         code,
 11         nvl(start_date, first_value(start_date) ignore nulls over (order by null)) start_date,
 12         nvl(end_date  , first_value(start_date) ignore nulls over (order by null)) end_date
 13  from test
 14  order by code;

ID_ACTIVITY COD START_DATE END_DATE
----------- --- ---------- ----------
          4 A00 01/10/2001 01/10/2001
          7 A01 01/10/2001 01/10/2001
          6 A02 01/10/2001 01/10/2001
          3 A03 01/10/2001 06/30/2002
          4 A04 10/04/2001 07/31/2004

SQL>