There is a table which has 2 date columns as follows:-

date1 date2
05-10-07 15-10-07
10-10-07 20-10-07

The output should be as follows:-

date1 date2 count (no.of days between the date range)
01-10-07 03-10-07 3
05-10-07 20-10-07 16

Query
-------

drop table t purge;
create table t (date1 date, date2 date);
truncate table t;insert into t select dt-dbms_random.value(1,9) date1, dt date2from (select sysdate-dbms_random.value(1+5*level,3+5*level) dt from dual connect by level <= 10);

SQL> with
step1 as (
select date1, date2, case when nvl(lag(date2) over (order by date1, date2), date1-1) < date1 then row_number() over (order by date1, date2) end flag from t ),
step2 as (
select date1, date2, max(flag) over (order by date1, date2) grp from step1)
select min(date1) date1, max(date2) date2, round(max(date2)-min(date1)) daycount from step2 group by grp order by 1;