Найти повторяющее значение

with t as (
select 1 id, 0 ves, sysdate dd from dual union all
select 1 id, 10 ves, sysdate+2 dd from dual union all
select 2 id, 0 ves, sysdate+3 dd from dual union all
select 2 id, 0 ves, sysdate+4 dd from dual union all
select 1 id, 10 ves, sysdate+5 dd from dual union all
select 1 id, 0 ves, sysdate+6 dd from dual union all
select 2 id, 10 ves, sysdate+7 dd from dual union all
select 2 id, 10 ves, sysdate+8 dd from dual union all
select 2 id, 0 ves, sysdate+9 dd from dual union all
select 2 id, 0 ves, sysdate+10 dd from dual union all
select 1 id, 0 ves, sysdate+11 dd from dual union all
select 1 id, 10 ves, sysdate+12 dd from dual union all
select 2 id, 0 ves, sysdate+13 dd from dual
)

SQL> with t as (
  2  select 1 id, 0 ves, sysdate dd from dual union all
  3  select 1 id, 10 ves, sysdate+2 dd from dual union all
  4  select 2 id, 0 ves, sysdate+3 dd from dual union all
  5  select 2 id, 0 ves, sysdate+4 dd from dual union all
  6  select 1 id, 10 ves, sysdate+5 dd from dual union all
  7  select 1 id, 0 ves, sysdate+6 dd from dual union all
  8  select 2 id, 10 ves, sysdate+7 dd from dual union all
  9  select 2 id, 10 ves, sysdate+8 dd from dual union all
 10  select 2 id, 0 ves, sysdate+9 dd from dual union all
 11  select 2 id, 0 ves, sysdate+10 dd from dual union all
 12  select 1 id, 0 ves, sysdate+11 dd from dual union all
 13  select 1 id, 10 ves, sysdate+12 dd from dual union all
 14  select 2 id, 0 ves, sysdate+13 dd from dual
 15  )
 16  select id, ves, dd from
 17  (select t.*,
 18  lag(ves) over(partition by id order by dd) lag_ves,
 19  lead(ves) over(partition by id order by dd) lead_ves
 20  from t)
 21  where ves = 0 and (ves = lag_ves or ves = lead_ves)
 22  /
 
        ID        VES DD
---------- ---------- -----------
         1          0 10.12.2007
         1          0 15.12.2007
         2          0 07.12.2007
         2          0 08.12.2007
         2          0 13.12.2007
         2          0 14.12.2007
         2          0 17.12.2007
 
7 rows selected
 
SQL> 

with t as (
select 1 id, 0 ves, sysdate dd from dual union all
select 1 id, 10 ves, sysdate+2 dd from dual union all
select 2 id, 0 ves, sysdate+3 dd from dual union all
select 2 id, 0 ves, sysdate+4 dd from dual union all
select 1 id, 10 ves, sysdate+5 dd from dual union all
select 1 id, 0 ves, sysdate+6 dd from dual union all
select 2 id, 10 ves, sysdate+7 dd from dual union all
select 2 id, 10 ves, sysdate+8 dd from dual union all
select 2 id, 0 ves, sysdate+9 dd from dual union all
select 2 id, 0 ves, sysdate+10 dd from dual union all
select 1 id, 0 ves, sysdate+11 dd from dual union all
select 1 id, 10 ves, sysdate+12 dd from dual union all
select 2 id, 0 ves, sysdate+13 dd from dual
),
t2 AS (
SELECT t.*
      ,CASE
         WHEN ves = 0
              AND
              (LEAD(ves) OVER(PARTITION BY ID ORDER BY dd) = 0 OR LAG(ves)
               OVER(PARTITION BY ID ORDER BY dd) = 0) THEN
          1
         ELSE
          0
       END f
  FROM t)
SELECT * FROM t2 WHERE f=1

with t as (
select 1 id, 0 ves, sysdate dd from dual union all
select 1 id, 10 ves, sysdate+2 dd from dual union all
select 2 id, 0 ves, sysdate+3 dd from dual union all
select 2 id, 0 ves, sysdate+4 dd from dual union all
select 1 id, 10 ves, sysdate+5 dd from dual union all
select 1 id, 0 ves, sysdate+6 dd from dual union all
select 2 id, 10 ves, sysdate+7 dd from dual union all
select 2 id, 10 ves, sysdate+8 dd from dual union all
select 2 id, 0 ves, sysdate+9 dd from dual union all
select 2 id, 0 ves, sysdate+10 dd from dual union all
select 1 id, 0 ves, sysdate+11 dd from dual union all
select 1 id, 10 ves, sysdate+12 dd from dual union all
select 2 id, 0 ves, sysdate+13 dd from dual
)

select k.id, k.ves, k.dd from(select w.*, LEAD(w.ves, 1) OVER (ORDER BY w.id, w.dd) as ves_next,
LAG(w.ves, 1) OVER (ORDER BY w.id, w.dd) AS ves_prev from(
select 1 id, 0 ves, sysdate dd from dual union all
select 1 id, 10 ves, sysdate+2 dd from dual union all
select 2 id, 0 ves, sysdate+3 dd from dual union all
select 2 id, 0 ves, sysdate+4 dd from dual union all
select 1 id, 10 ves, sysdate+5 dd from dual union all
select 1 id, 0 ves, sysdate+6 dd from dual union all
select 2 id, 10 ves, sysdate+7 dd from dual union all
select 2 id, 10 ves, sysdate+8 dd from dual union all
select 2 id, 0 ves, sysdate+9 dd from dual union all
select 2 id, 0 ves, sysdate+10 dd from dual union all
select 1 id, 0 ves, sysdate+11 dd from dual union all
select 1 id, 10 ves, sysdate+12 dd from dual union all
select 2 id, 0 ves, sysdate+13 dd from dual
order by 1,3
)w)k
where k.ves=0 and (k.ves_next=0 or k.ves_prev = 0)