Create table a(mm varchar2(100), val number, dt date);
 
 Insert into a values (1, 10, to_date('04/01/2010','mm/dd/yyyy'));
 Insert into a values (1, 11, to_date('04/02/2010','mm/dd/yyyy'));
 Insert into a values (1, 17, to_date('04/07/2010','mm/dd/yyyy'));
 Insert into a values (1, 10, to_date('05/01/2010','mm/dd/yyyy'));
 Insert into a values (1, 11, to_date('05/02/2010','mm/dd/yyyy'));
 Insert into a values (1, 11, to_date('06/01/2010','mm/dd/yyyy'));
 Insert into a values (1, 12, to_date('07/01/2010','mm/dd/yyyy'));
 Insert into a values (1, 13, to_date('08/01/2010','mm/dd/yyyy'));
 Insert into a values (1, 14, to_date('09/01/2010','mm/dd/yyyy'));
 Insert into a values (1, 14, to_date('09/09/2010','mm/dd/yyyy'));
 
 
 
 I need to find id's and first occurence of first date and last date that have value less than 15 for atleast 90  days minimun.
 
 O/p would be :
 
 1, 10, 05/01/2010 
 1, 12, 08/01/2010

SCOTT@orcl_12.1.0.2.0> WITH
  2    a2 AS
  3  	 ( SELECT mm, val
  4  		, LEAD (dt) OVER (PARTITION BY mm ORDER BY dt) AS lead_dt
  5  	   FROM   a)
  6  , a4 AS
  7  	 ( SELECT a.mm AS a_mm, a.val AS a_val, a.dt
  8  		, a2.mm AS a2_mm, a2.val AS a2_val, a2.lead_dt
  9  		, ROW_NUMBER () OVER (PARTITION BY a.mm ORDER BY a.dt, a2.lead_dt) rn
 10  	   FROM   a, a2
 11  	   WHERE  a.mm = a2.mm
 12  	   AND	  a2.lead_dt >= a.dt + 90
 13  	   AND	  NOT EXISTS
 14  		  ( SELECT a3.*
 15  		    FROM   a a3
 16  		    WHERE  a3.mm = a.mm
 17  		    AND    a3.dt BETWEEN a.dt AND a2.lead_dt
 18  		    AND    a3.val >= 15
 19  		  )
 20  	 )
 21  SELECT a4.a_mm AS mm, a4.a_val AS val, dt
 22  FROM   a4
 23  WHERE  rn = 1
 24  UNION ALL
 25  SELECT a4.a2_mm AS mm, a4.a2_val AS val, lead_dt dt
 26  FROM   a4
 27  WHERE  rn = 1
 28  ORDER  BY mm, dt
 29  /

MM                VAL DT
---------- ---------- ----------
1                  10 05/01/2010
1                  12 08/01/2010

2 rows selected.

with t1 as (
            select  a.*,
                    count(case when a.val >= 15 then 1 end) over(partition by a.mm order by a.dt) grp,
                    lead(a.dt) over(partition by mm order by a.dt) end_dt
              from  a
           ),
     t2 as (
            select  t1.*,
                    case
                      when     t1.dt = min(t1.dt) over(partition by t1.grp)
                           and
                               max(t1.end_dt) over(partition by t1.grp) - t1.dt > 90
                        then 1
                      when     t1.dt - min(t1.dt) over(partition by t1.grp) < 90
                           and
                               t1.end_dt - min(t1.dt) over(partition by t1.grp) >= 90
                        then 2
                    end flag
              from  t1
              where t1.val < 15
           )
select  t2.mm,
        t2.val,
        case t2.flag
          when 1 then t2.dt
          else t2.end_dt
        end dt
  from  t2
  where t2.flag > 0
  order by t2.mm,
           t2.dt
/

MM                VAL DT
---------- ---------- ----------
1                  10 05/01/2010
1                  12 08/01/2010

SQL>