SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

MIN and MAX date for each range

gerd_99Oct 5 2005 — edited Oct 6 2005

Hi folks,

I´ve got the following problem:

Given a table

create table t as select 'AT' OM_LANDTEXT, '12345' OM,
                         to_date('01-JAN-2004', 'DD-MON-YYYY') datum from dual
                         union
                         select 'AT' OM, '12345' OM_LANDTEXT,
                         to_date('02-JAN-2004', 'DD-MON-YYYY') datum from dual
                         union
                         select 'AT' OM, '12345' OM_LANDTEXT,
                         to_date('03-JAN-2004', 'DD-MON-YYYY') datum from dual
                         union
                         select 'AT' OM, '12345' OM_LANDTEXT,
                         to_date('05-JAN-2004', 'DD-MON-YYYY') datum from dual
                         union
                         select 'AT' OM, '12345' OM_LANDTEXT,
                         to_date('06-JAN-2004', 'DD-MON-YYYY') datum from dual
                         union
                         select 'AT' OM, '12345' OM_LANDTEXT,
                         to_date('01-FEB-2004', 'DD-MON-YYYY') datum from dual
                         union
                         select 'AT' OM, '12345' OM_LANDTEXT,
                         to_date('02-FEB-2004', 'DD-MON-YYYY') datum from dual
                         union
                         select 'AT' OM, '12345' OM_LANDTEXT,
                         to_date('03-FEB-2004', 'DD-MON-YYYY') datum from dual;

SQL> select * from t;

OM OM    DATUM
-- ----- ---------
AT 12345 01-JAN-04
AT 12345 02-JAN-04
AT 12345 03-JAN-04
AT 12345 05-JAN-04
AT 12345 06-JAN-04
AT 12345 01-FEB-04
AT 12345 02-FEB-04
AT 12345 03-FEB-04

8 rows selected.

I need to have the first and last date for om and om_landtext for each date range (where "date range" means a set of consecutive dates with no gaps).

So the ouput should be

OM OM_LANDTEXT DATVON DATBIS
-----------------------------
AT 12345     01-JAN-04 03-JAN-04
AT 12345     05-JAN-04 06-JAN-04
AT 12345     01-FEB-04 03-FEB-04

Any ideas...?

Thanks for your help!

Best regards,
Gerd

Message was edited by: Gerd

Sorry, I forgot to mention this should be done in pure SQL if possible.
gerd.reinoehl@gmx.at

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 3 2005

Added on Oct 5 2005

9 comments

1,371 views