Summarizes the start and end dates (or how to eleminate overlaps in dates)

TimeSheet table
StartDate EndDate
---------- ----------
2005-01-01 2005-01-03
2005-01-02 2005-01-04
2005-01-04 2005-01-05
2005-01-06 2005-01-09
2005-01-09 2005-01-09
2005-01-12 2005-01-15
2005-01-13 2005-01-14
2005-01-14 2005-01-14
2005-01-17 2005-01-17

result
StartDate EndDate
---------- ----------
2005-01-01 2005-01-05
2005-01-06 2005-01-09
2005-01-12 2005-01-15
2005-01-17 2005-01-17


Data creation script

create table TimeSheets(
StartDate Date,
EndDate   Date,
primary key (StartDate));

insert into TimeSheets values(to_date('20050101','yyyymmdd'),to_date('20050103','yyyymmdd'));
insert into TimeSheets values(to_date('20050102','yyyymmdd'),to_date('20050104','yyyymmdd'));
insert into TimeSheets values(to_date('20050104','yyyymmdd'),to_date('20050105','yyyymmdd'));
insert into TimeSheets values(to_date('20050106','yyyymmdd'),to_date('20050109','yyyymmdd'));
insert into TimeSheets values(to_date('20050109','yyyymmdd'),to_date('20050109','yyyymmdd'));
insert into TimeSheets values(to_date('20050112','yyyymmdd'),to_date('20050115','yyyymmdd'));
insert into TimeSheets values(to_date('20050113','yyyymmdd'),to_date('20050114','yyyymmdd'));
insert into TimeSheets values(to_date('20050114','yyyymmdd'),to_date('20050114','yyyymmdd'));
insert into TimeSheets values(to_date('20050117','yyyymmdd'),to_date('20050117','yyyymmdd'));
commit;

SQL

Solution 1
How to use an inline view

select StartDate,min(EndDate) as EndDate
from
(select a.StartDate,b.EndDate
   from TimeSheets a,TimeSheets b
  where a.EndDate     and not exists(select 1 from TimeSheets c
                    where (c.StartDate <  a.StartDate and a.StartDate                        or (c.StartDate group by StartDate
order by StartDate;

Solution 2
How to use an inline view

select a.StartDate,min(b.EndDate) as EndDate
   from TimeSheets a,TimeSheets b
  where a.EndDate     and not exists(select 1 from TimeSheets c
                    where (c.StartDate <  a.StartDate and a.StartDate                        or (c.StartDate group by a.StartDate
order by a.StartDate;

Solution 3
How to use an inline view

SELECT startdate, MIN(enddate) AS enddate
  FROM (SELECT T1.startdate, T2.enddate
          FROM Timesheets T1, Timesheets T2
         WHERE T1.enddate  T3.startdate AND T1.startdate = T3.startdate AND T2.enddate < T3.enddate)))
 GROUP BY startdate;

Solution 4
How to use an inline view

SELECT startdate, MIN(enddate) AS enddate
  FROM (SELECT T1.startdate AS startdate, T2.enddate AS enddate
          FROM Timesheets T1, Timesheets T2, Timesheets T3
         WHERE T1.enddate  T3.startdate AND T1.startdate = T3.startdate AND T2.enddate < T3.enddate) THEN 1 END) = 0)
 GROUP BY startdate;

Solution 5
How to use a hierarchical queries (> 10g)

select min(connect_by_root StartDate) as StartDate,EndDate
  from TimeSheets
 where connect_by_IsLeaf = 1
connect by nocycle prior EndDate between StartDate and EndDate
group by EndDate
order by StartDate;

Solution 6
How to use hierarchical queries (> 10g)

select connect_by_root StartDate as StartDate,EndDate
  from TimeSheets a
 where connect_by_IsLeaf = 1
start with not exists(select 1 from TimeSheets b
                       where a.RowID != b.RowID
                         and a.StartDate between b.StartDate and b.EndDate)
connect by nocycle prior EndDate between StartDate and EndDate
order by StartDate;

Solution 7
How to use analysis of the relevant number range

select min(StartDate) as StartDate,max(EndDate) as EndDate
from (select StartDate,EndDate,
      sum(willSum) over(order by StartDate) as GID
      from (select StartDate,EndDate,
            case when StartDate
                                                 range between unbounded preceding
                                             and 1 preceding)
                 then 0 else 1 end as willSum
              from TimeSheets))
group by GID
order by StartDate;

from

Advertisement