create table acc (accid number(10))
Table created.
create table accact ( accactid number(10), accid number(10), datetime date )
Table created.
create table accupd (accupdid number(10), accactid number(10), dispid number(10), dictionary number(10))
Table created.
create table disp (dispid number(10), flag varchar2(1))
Table created.
select acc.accid from acc
where ( select count(distinct(trunc(datetime)))
from accact
join accupd on accupd.accupdid = accact.accactid
join disp on disp.dispid = accupd.dispid
where accact.accactid = acc.accid
and accupd.dictionary = 12345
and disp.flag = 'Y'
and trunc(accact.datetime) between (trunc(sysdate) - 3) and trunc(sysdate) - 1) >= 3
and ( select count(distinct(trunc(datetime)))
from accact
join accupd on accupd.accupdid = accact.accactid
join disp on disp.dispid = accupd.dispid
where accact.accactid = acc.accid
and accupd.dictionary = 12345
and disp.flag = 'N'
and trunc(accact.datetime) between (trunc(sysdate) - 3) and trunc(sysdate) - 1) = 0
no data found