create table incident_registry
( date_of_occurrence date
, description varchar2(50)
)

INSERT INTO "SCOTT"."INCIDENT_REGISTRY" (DATE_OF_OCCURRENCE, DESCRIPTION) VALUES (TO_DATE('18-JUN-11', 'DD-MON-RR'), 'Ran over laptop with car')
INSERT INTO "SCOTT"."INCIDENT_REGISTRY" (DATE_OF_OCCURRENCE, DESCRIPTION) VALUES (TO_DATE('24-JUN-11', 'DD-MON-RR'), 'Cut finger while peeling potatoes')
INSERT INTO "SCOTT"."INCIDENT_REGISTRY" (DATE_OF_OCCURRENCE, DESCRIPTION) VALUES (TO_DATE('24-JUN-11', 'DD-MON-RR'), 'Hurt toe against kitchen door')
INSERT INTO "SCOTT"."INCIDENT_REGISTRY" (DATE_OF_OCCURRENCE, DESCRIPTION) VALUES (TO_DATE('18-JUL-11', 'DD-MON-RR'), 'Fell over when tangled in tent lines')
INSERT INTO "SCOTT"."INCIDENT_REGISTRY" (DATE_OF_OCCURRENCE, DESCRIPTION) VALUES (TO_DATE('21-JUL-11', 'DD-MON-RR'), 'Stomach issues after joyless seefood dinner')
INSERT INTO "SCOTT"."INCIDENT_REGISTRY" (DATE_OF_OCCURRENCE, DESCRIPTION) VALUES (TO_DATE('24-JUL-11', 'DD-MON-RR'), 'Walked into door (black eye)')
INSERT INTO "SCOTT"."INCIDENT_REGISTRY" (DATE_OF_OCCURRENCE, DESCRIPTION) VALUES (TO_DATE('01-aug-11', 'DD-MON-RR'), 'Sunburnt')

select date_of_occurrence "Start of incident free period"
,      lead(date_of_occurrence) over (order by date_of_occurrence ) "Length of period"
from   incident_registry


select *
from   ( select date_of_occurrence "Start of incident free period"
         ,      lead(date_of_occurrence) over (order by date_of_occurrence )  - date_of_occurrence "Length of period"
         from   incident_registry
         order
         by     2 desc nulls last
       )
where  rownum = 1  



create table instances
( instance_date date
, description varchar2(200)
)


INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('08-FEB-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('08-FEB-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('09-FEB-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('10-FEB-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('11-FEB-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('13-APR-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('14-APR-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('16-APR-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('22-APR-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('22-APR-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('18-MAY-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('19-MAY-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('20-MAY-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('20-MAY-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('21-MAY-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('22-MAY-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('23-MAY-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('23-MAY-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('25-MAY-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('10-JUN-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('19-JUN-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('20-JUN-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('22-JUN-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('15-JUL-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('02-AUG-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('17-AUG-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('18-AUG-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('19-AUG-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('20-AUG-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('22-AUG-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('23-AUG-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('09-sep-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('10-sep-11', 'DD-MON-RR'))
INSERT INTO "SCOTT"."INSTANCES" (INSTANCE_DATE) VALUES (TO_DATE('12-sep-11', 'DD-MON-RR'))



select instance_date
,      instance_date - lag(instance_date) over (order by instance_date)
,      lead(instance_date) over (order by instance_date) - instance_date 
from   instances



select instance_date
                ,      1 +lead(case when before_next >1 then instance_date end) ignore nulls over (order by instance_date )
                       - instance_date streak_duration
                ,      since_previous
                ,      before_next
                from ( select instance_date
                       ,      instance_date - lag(instance_date) over (order by instance_date) since_previous
                       ,      lead(instance_date) over (order by instance_date) - instance_date before_next
                       from   instances
                     )

select instance_date
,      streak_duration
from   ( select *
         from ( select instance_date
                ,      1 +lead(case when before_next >1 then instance_date end) ignore nulls over (order by instance_date )
                       - instance_date streak_duration
                ,      since_previous
                ,      before_next
                from ( select instance_date
                       ,      instance_date - lag(instance_date) over (order by instance_date) since_previous
                       ,      lead(instance_date) over (order by instance_date) - instance_date before_next
                       from   instances
                     )
              )
         where  nvl(since_previous,2) > 1
         and    before_next < 2
         order
         by     streak_duration desc
       ) 
where  rownum = 1