Select b1."BENEFIT_KEY",b1."DIVISION_KEY",b1."DISCIPLINE_KEY",b1."BENEFIT_COVERAGE_DT",b1."BENEFIT_DESC",b1."BENEFIT_DEPEND_AGE1_NUM",b1."BENEFIT_DEPEND_AGE2_NUM",b1."HSA_SERVICE_DT_IND",b1."EFFECTIVE_ON",b1."EFFECTIVE_BY",b1."EXPIRED_ON",b1."EXPIRED_BY",b1."CREATED_ON",b1."CREATED_BY",b1."UPDATED_ON",b1."UPDATED_BY", 'C' Status From v_benefit b1
Where trunc(sysdate) between b1.effective_on and nvl(b1.expired_on, sysdate)                               
/* Futrue One */
Union All
Select b2."BENEFIT_KEY",b2."DIVISION_KEY",b2."DISCIPLINE_KEY",b2."BENEFIT_COVERAGE_DT",b2."BENEFIT_DESC",b2."BENEFIT_DEPEND_AGE1_NUM",b2."BENEFIT_DEPEND_AGE2_NUM",b2."HSA_SERVICE_DT_IND",b2."EFFECTIVE_ON",b2."EFFECTIVE_BY",b2."EXPIRED_ON",b2."EXPIRED_BY",b2."CREATED_ON",b2."CREATED_BY",b2."UPDATED_ON",b2."UPDATED_BY", 'F' Status From v_benefit b2
Where b2.effective_on =
    (select max(bn1.effective_on)
     from v_benefit bn1
     where bn1.DIVISION_KEY = b2.DIVISION_KEY
     and bn1.DISCIPLINE_KEY = b2.DISCIPLINE_KEY
    )
    and exists
    (select  1  from v_benefit bn2
     where bn2.DIVISION_KEY = b2.DIVISION_KEY
     and bn2.DISCIPLINE_KEY = b2.DISCIPLINE_KEY
     and bn2.effective_on >= Trunc(sysdate)
    )
    and not exists
    (select  1  from v_benefit bn3
     Where bn3.DIVISION_KEY = b2.DIVISION_KEY
       and bn3.DISCIPLINE_KEY = b2.DISCIPLINE_KEY
       and trunc(sysdate) between bn3.effective_on and nvl(bn3.expired_on, sysdate)
    )
/*Last Expired One */
Union All
Select b3."BENEFIT_KEY",b3."DIVISION_KEY",b3."DISCIPLINE_KEY",b3."BENEFIT_COVERAGE_DT",b3."BENEFIT_DESC",b3."BENEFIT_DEPEND_AGE1_NUM",b3."BENEFIT_DEPEND_AGE2_NUM",b3."HSA_SERVICE_DT_IND",b3."EFFECTIVE_ON",b3."EFFECTIVE_BY",b3."EXPIRED_ON",b3."EXPIRED_BY",b3."CREATED_ON",b3."CREATED_BY",b3."UPDATED_ON",b3."UPDATED_BY", 'T' Status From v_benefit b3
Where b3.effective_on =
    (select max(bn1.effective_on)
     from v_benefit bn1
     where bn1.DIVISION_KEY = b3.DIVISION_KEY
     and bn1.DISCIPLINE_KEY = b3.DISCIPLINE_KEY
    )
    and Not exists
    (select  1  from v_benefit bn2
     where bn2.DIVISION_KEY = b3.DIVISION_KEY
     and bn2.DISCIPLINE_KEY = b3.DISCIPLINE_KEY
     and bn2.effective_on >= Trunc(sysdate)
    )
    and not exists
    (select  1  from v_benefit bn3
     Where bn3.DIVISION_KEY = b3.DIVISION_KEY
       and bn3.DISCIPLINE_KEY = b3.DISCIPLINE_KEY
       and trunc(sysdate) between bn3.effective_on and nvl(bn3.expired_on, sysdate)
    )

select d1.* ----> в почту DBA
from data d1, data d2 
where d1.id_объекта = d2.id_объекта 
and d1.id_записи <> d2.id_записи
and d1.date_start <= d2.date_finish
and d1.date_finish => d2.date_start